Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am fairly new to Qlikview, and know a little bit of set expressions. I was trying to achieve something where in i want to calculate the distinct counts in a pivot, but the catch being, the range for distinct count should be all dates <= the date that i am pivoting on.
I tried to achieve this using set analysis, but it doesnt work, wanted to check with the community if there is a better way of achieving it.
My data would be something like below:
Date | Field 1 | Field 2 | Field 3 |
01/01/2020 | A | B | 200 |
01/01/2020 | A | C | 200 |
01/01/2020 | A | B | 27 |
01/01/2020 | A | C | 27 |
01/01/2020 | A | B | 200 |
01/01/2020 | A | B | 27 |
02/02/2020 | B | B | 200 |
02/02/2020 | B | C | 200 |
02/02/2020 | A | B | 27 |
02/02/2020 | A | C | 27 |
02/02/2020 | B | B | 200 |
02/02/2020 | B | B | 27 |
I am looking at taking a distinct count of Field 1 and 2 on the range of dates <= given date:
the expected outcome is as below:
This is a pivot
Date | Field 3 | Counts (Rolling dates) |
01/01/2020 | 200 | 2 |
27 | 2 | |
02/01/2020 | 200 | 2 |
27 | 1 |
Can someone help me to understand the best way to achieve this. Tried multiple posts on the community and wasn't able to come close to the requirement.
Thanks in advance.
Kris
ah ok
attached new version
=count(aggr(min(Date),[Field 1],[Field 2],[Field 3]))
Hi,
I don't know if I understood your post !
because I feel like count() is enough
=count(distinct [Field 1]&[Field 2])
attached a qlikview file
I didn't get the 1 for the 27 in 02/01/2020 since you have A B , A C and BB
??
Precisely why plain count doesnt work,
I want to check all previous dates if the combination exists and take just the unique counts, so since AB AC are already on the earlier date, i just want to consider BB 27 and hence the count 1.
Was able to build the logic in SQL by using not exists, but lost here.
Thanks for the suggestion though.
-Kris.
ah ok
attached new version
=count(aggr(min(Date),[Field 1],[Field 2],[Field 3]))
If Taoufiq's last post and example got you what you needed, do not forget to properly close out your thread by returning on on that last post of his, use the Accept as Solution button to mark it, which gives him credit for the help and lets other Members know that worked.
The other place you will likely find helpful going forward is the following, lots of how-to posts here from our internal experts:
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
Regards,
Brett