# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for
Did you mean:
Not applicable

## Rolling n number of days (Distinct) - Serious issue

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:

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.

1 Solution

Accepted Solutions
MVP & Luminary

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

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:

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

talk is cheap, supply exceeds demand
8 Replies
Not applicable

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

MVP & Luminary

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

talk is cheap, supply exceeds demand
Not applicable

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

1       2

2       5

3       6

4       7

Not working, thank you for your suggestion.

Not applicable

Thank you but this isn't working

MVP & Luminary

It returns this here:

Day     Count

1          2

2          3

3          3

4          3

talk is cheap, supply exceeds demand
Not applicable

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.

MVP & Luminary

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

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:

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

talk is cheap, supply exceeds demand
Not applicable

You are amazing. This work! Thanks a ton!

Tags