7 Replies Latest reply on Nov 12, 2018 2:11 PM by Michel Caissie

# Count distinct on moving calculation

Hi!

I'm cooking a report, where I need moving calculation.

Here is how I'm counting purchases: WINDOW_SUM( COUNTD( [purchase_id]), [Size of movie period], 0)

[Size of movie period] is a parameter, to change the period.

Everything is counted correctly, but when I try to count distinct of clients ID, I meet a problem.

At first I count it like:

WINDOW_SUM( COUNTD( [CLIENT_ID]), [Size of movie period], 0), but found that it as incorrect, as he counts distinct at each month and sum them, so I get lots of doubles and incorrect number of clients.

Are there any ideas?

Unfortunately I can't share workbook as it will take to much time to generate fake data for it, but I'll attach a screenshot.

• ###### 1. Re: Count distinct on moving calculation

Alexey,

You can try something like this;

1- you can find the date of the clients first purchase per Category

{FIXED [CLIENT_ID], [Category]: MIN[Date] }

2- you can then create a new Client dimension  returning the client only on its first purchase date

if date = {FIXED [CLIENT_ID], [Category]: MIN[Date] } then [CLIENT_ID]  end

3- so the final calculation would be

WINDOW_SUM( COUNTD(if date = {FIXED [CLIENT_ID], [Category]: MIN[Date] } then [CLIENT_ID]  end), [Size of movie period], 0)

If it doesn't work , you don't need to generate fake data,  you can mockup something similar using  Superstore.

Michel

1 of 1 people found this helpful
• ###### 2. Re: Count distinct on moving calculation

Hi Alexey,

TOTAL(COUNTD(CLIENT_ID) )

It should make your life easier , if it solves your problem then I would advise you to see this as well --->Total() vs Window_Sum()

Thanks,

Ritesh

Please mark the answer as CORRECT & HELPFUL if it really helps you so that it can help others as well

1 of 1 people found this helpful
• ###### 3. Re: Count distinct on moving calculation

Thanks a lot, Michel Caissie!

Unfortunately it didn't help.

I created the same as you advised on the superstore base.

• ###### 4. Re: Count distinct on moving calculation

Thanks!

Unfortunately it will not help me, as it will not take rolling period into action.

• ###### 5. Re: Count distinct on moving calculation

Check in the attached and let me know if it is the numbers you expect.

1 of 1 people found this helpful
• ###### 6. Re: Count distinct on moving calculation

Thanks! That's what I was looking for, but after adding category - numbers gone creasy =(

Here is file.

I added it to FIXED, but you see the result.

• ###### 7. Re: Count distinct on moving calculation

Alexey,

I made some changes to Category(1) (copy)  and  Rolling clients (copy).

If it is still not what you are looking for, please let me know  what numbers you expect for each measure for the first 3 months.