Cumulative count distinct

[#1]

Hi guys,

I have to calculate a cumulative count distinct from the very beginning of my data. The "distinct" has to consider the whole data set of course.

I am currently using this formula:

if(count(distinct [Number of Active Suppliers])<>0,num(count({<MonthYear={'<=$(=max(MonthYear))'},Year,Month>} total <MonthYear>distinct [Number of Active Suppliers]), '#.##0'))

which is fine for monthly values, but it does not work when I make multiple selections on months (for every month I get the value of the last one selected).

Any idea?

Many thanks,

Matteo

[Gysbert_Wassenaar]

I think you'll have to use an AsOf table in this case. With sums you have other options but those don't work for a cumulative count distinct. Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

[#3]

Hi Gysbert,

I tried the AsOf table as you suggested, in particular using this process:

The As-Of Table

I can accumulate intra-year values very easily, but how can I use this approach to get accumulated values from 2004 up to now?

Thanks,

M

[#4]

Gysbert,

I just noticed that you commented Henric's blog regarding my specific problem. In fact the biggest problem I am facing at the moment is that the selections on the dashboard must be made on "normal month" and "normal year" fields.

So, as you correctly pointed out in your comment, it seems that AsOfMonth field works on the contrary if the selections are made on "normal" date fields.

Have you developed a work around for this situation?

At the moment, my formula is:

count({<YearDiff={'<=13'},AsOfMonth={'<=$(=min(AsOfMonth))'},Year,Month>}  distinct [Number of Active Suppliers])

The issues are:

- make the set analysis select the proper value for AsOfMonth (for multiple selections as well)

- showing values ONLY for the selected year, maintaining the accumulation

Many thanks.

M

[Gysbert_Wassenaar]

Perhaps something like this:

count({<AsOfMonth={'>=$(=Date(MakeDate(2014),'YYYY MMM'))<=$(=Date(Today(),'YYYY MMM'))'},Year,Month>}  distinct [Number of Active Suppliers])

You may have to adjust the date format string to whatever your AsOfMonth values have

[Gysbert_Wassenaar · Accepted solution]

- make the set analysis select the proper value for AsOfMonth (for multiple selections as well)

- showing values ONLY for the selected year, maintaining the accumulation

If you want to use the selections in your regular Year and Month fields as selections for the AsOf fields then do something like:

count({<AsOfYear=P(Year),AsOfMonth=P(Month),Year,Month>}  distinct [Number of Active Suppliers])

[#7]

Hi Gysbaert,

that's exactly how I solved this issue just some hours before your answer

Thanks!

M