Tableau pipeline, count ID for calculation only once per calculation

Question

I am trying to make a funnel/pipeline for a data set in Tableau. (Must use tableau as the company has a dashboard portal for internal users)

I have data set up as such:

id|app date|group|date start
1 |monday  |3    |   null
2 |monday  |null |   null
3 |tuesday |3    |   wednesday
3 |tuesday |3    |   wednesday
4 |null    |null |   null

Due to other features in the data set, rows for this particular problem will be repeated as shown with id 3 above. Also due to other problems in the dashboard I need to retain the other information causing the duplication of rows.

For my pipeline to be accurate, I need to count each instance once.

For example, 4 prospects, 3 applicants, 2 accepted, 1 started in the data above. I am having difficulty creating the calculations in order to do this.

I have tried several methods but have been unsuccessful thus far. I am very new to working with tableau calculations and cannot figure out how to use its built in functions to achieve my goal. Is there a way to use SQL statements within a calculation? I would be able to solve my problem if this is possible.

S. User18 S. User18 · Accepted Answer · 2018-01-10T19:02:29

You can make calculated fields to count distinct id, ignoring values where a given column is null (which I assume is the criteria for whether the row meets the condition--it wasn't perfectly clear from your question).

These four calculated fields count number of rows that meet each condition per your example.

num_prospects: COUNTD(IIF(ISNULL([Id]),NULL,[Id]))
num_applicants: COUNTD(IIF(ISNULL([App Date]),NULL,[Id]))
num_accepted: COUNTD(IIF(ISNULL([Group]),NULL,[Id]))
num_started: COUNTD(IIF(ISNULL([Date Start]),NULL,[Id]))

Tableau pipeline, count ID for calculation only once per calculation

2 Answers