Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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

Re: Rolling n number of days (Distinct) - Serious issue

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
8 Replies
Not applicable

Re: Rolling n number of days (Distinct) - Serious issue

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

Re: Rolling n number of days (Distinct) - Serious issue

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

Re: Rolling n number of days (Distinct) - Serious issue

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

Re: Rolling n number of days (Distinct) - Serious issue

Thank you but this isn't working

MVP & Luminary
MVP & Luminary

Re: Rolling n number of days (Distinct) - Serious issue

It returns this here:

Day     Count

1          2

2          3

3          3

4          3


talk is cheap, supply exceeds demand
Not applicable

Re: Rolling n number of days (Distinct) - Serious issue

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

Re: Rolling n number of days (Distinct) - Serious issue

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

Re: Rolling n number of days (Distinct) - Serious issue

You are amazing. This work! Thanks a ton!