I have a data set as shown below
I want to display a running distinct customer count across the dates when the criteria is met.
So final output should be shown below
I have attached the twbx file as well as the dataset.
I want the rolling count of companies,
see below:
6/6/2018 count will be 1, company DEF
6/13/2018, it will be 2, XYZ & DEF
6/15/2018, it will still be 2 because we already counted XYZ and DEF
7/5/2018, it will still be 2 because we already counted XYZ and DEF
7/6/2018, it will be 3, XYZ, DEF & ABC
Before starting, I'd suggest that you change Start Date to an actual date data type. Next, create the following calculated fields:
Customer First Date Met
// Use an LOD to get the first date on which the customer met the criteria.
{FIXED [Customer Id]: MIN(IIF([Criteria Met]="True", [Start Date], NULL))}
Criteria Met Count
// Get customer ID if that customer's first date matches this date.
IF [Start Date]=[Customer First Date Met] THEN
[Customer Id]
END
Count
// Count the number of customers meeting the criteria.
COUNTD([Criteria Met Count])
Finally, you can build your view. Drag Start Date to the Columns shelf. Then drag Count to the text card. Right-click on that pill and choose "Quick Table Calculation" followed by "Running Total". You should now have something like this, which I believe is what you're looking for. Workbook is attached.
