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

    Count distinct on moving calculation

    Alexey Panfilov

      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.