Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Try a straight table with DAY as dimension and as expression RangeMax(above(count(DISTINCT USERID),0,6))
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.
Thank you but this isn't working
It returns this here:
Day Count
1 2
2 3
3 3
4 3
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.
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
You are amazing. This work! Thanks a ton!