Rolling n number of days (Distinct) - Serious issue

[#1]

I have been a long time user of Qlik analytics software and it is a great software.

However, I would like to be able to have a running_total_distinct functionality.for n-days.

Example

DAY   USERID   Expected running_total_distinct for last 7 days

1         001           2

1         002           2

2         001           3

2         002           3

2         003           3

3         003           3

4         002           3

...

However, I have extensively looked throughout Qlik Community forums and there was no solution.

Is there a way I can achieve my goal of counting a distinct total.

I was able to achieve this with little to no effort using this software I found here with Tableau:

http://www.tableau.com/about/blog/LOD-expressions

On example #5 It provides the solution which works wonderfully.

It seems that switching to Tableau is the best and only viable option for me as I am frustrated searching for solution online for months.

Please guide me and get me out of this problem. Thanks.

[#2]

Hi Chaitanya

If I understood your question correctly and looking at the Tableau stuff you sent.

This should work for you - Please let me know.

See attached.

Kind Regards

[Gysbert_Wassenaar]

Try a straight table with DAY as dimension and as expression RangeMax(above(count(DISTINCT USERID),0,6))

[#4]

No, this does not achieve my goal.

This will count the distinct userID for each day and sum them up.

count(distinct userID)  will count a distinct user id

above((distinct userid),0,6) will count distinct user id per day and sum each day so it would give me

DAY, {{your expression}}

1       2

2       5

3       6

4       7

Not working, thank you for your suggestion.

[#5]

Thank you but this isn't working

[Gysbert_Wassenaar]

It returns this here:

Day     Count

1          2

2          3

3          3

4          3

[#7]

This is not the right answer. I will give you a counter-example.

It does not work for this dataset which is more common, and doesn't have an increasing pattern

DAY   USERID   Expected running_total_distinct for last 7 days

1         001           2

1         002           2

2         001           3

2         002           3

2         003           3

3         003           3

4         002           3

5         077           4      Your rangemax is essentially doing max in this group (2,3,1,1,1) which will give 3 and not 4

It is exceptionally clever, I will give you that. Very nice try, much appreciated for the effort though.

[Gysbert_Wassenaar · Accepted solution]

Yeah, counts are very hard to do this way. Sums aren't a problem using the rangesum function. But rangecount doesn't work in this case.

The easy way out is adding a simple AsOf table in the script:

TestData

LOAD * INLINE [

    DAY, USERID

    1, 001

    1, 002

    2, 001

    2, 002

    2, 003

    3, 003

    4, 002

    5, 077

    6, 002

    6, 077

    7, 077

    8, 002

    9, 002

  10, 002

  11, 002

  12, 002

  12, 001

];

AsOf:

LOAD DISTINCT

  DAY as RDAY,

  DAY +1 - IterNo() as DAY

RESIDENT

  TestData

WHILE IterNo() <= 7 AND DAY + 1 - IterNo() > 0

  ;

Then you can use RDAY as dimension and count(distinct USERID) as expression

[#9]

You are amazing. This work! Thanks a ton!