CODEWITHSUNDEEP
tableau-api
Nirmal
Nirmal

Reputation: 31

Find count of active users in the last 29 days in Tableau

Require assistance in calculating the Total Active Users from March 16 2020 to Feb 16 2020.

enter image description here

I have tried using calculated fields, but not getting the correct results. Please advise.

Thank you, Nirmal

Upvotes: 1

Views: 1496

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11896

To find the number of unique values that appear in a field, say [user_code], you can use the COUNT DISTINCT function, COUNTD() as in COUNTD([user_code])

To restrict the data to a particular time range, one way is put your date field on the Filter shelf and choose the settings that include only the data rows you want — say the range from 2/16 to 3/16 as you stated.

Alternatively, you can push the filtering condition into the calculation with an IF function call, as in COUNTD(IF <data is relevant> THEN [user_code] END) Thus effectively combining the two techniques. That works because if there is no ELSE clause and the IF condition is False then the IF statement evaluates to null. Since COUNTD() silently ignores nulls, like other aggregation functions, the expression acts as if the irrelevant data rows were filtered.

So, for example,

COUNTD(IF [dates] >= #2/16/2020# AND [dates] <= #3/16/2020# THEN [user_code] END)

Will tell you then number of unique user codes during the period between 2/16 and 3/16. The DateDiff() function will probably be useful in more elaborate tests.

Finally, what if you want more flexibility? You could easily use Parameters or Filter controls to let the user choose the date range interactively.

If you want this calculation repeated for each possible day, showing the unique users in the preceding 30 day period, as some sort of rolling calculation, then you’ll need to learn about some more advanced features. Either multiple calculations as above for different time ranges, using Table Calculations, or some data prep and/or data padding with Tableau Prep Builder, Python or some other technique — mostly because in that scenario each data row contributes to multiple rolling counts, rather than one count when partitioning the data by some dimension.

Upvotes: 1

Related Questions