Qlik Community

Ask a Question

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 
Search instead 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:

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.

1 Solution

Accepted Solutions
MVP & Luminary
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

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


talk is cheap, supply exceeds demand

View solution in original post

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
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

DAY, {{your expression}}

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
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
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

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


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

You are amazing. This work! Thanks a ton!