Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Mawkishrains
Contributor
Contributor

Expanding Ranges in Expression.

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:

DateField 1Field 2 Field 3
01/01/2020AB200
01/01/2020AC200
01/01/2020AB27
01/01/2020AC27
01/01/2020AB200
01/01/2020AB27
02/02/2020BB200
02/02/2020BC200
02/02/2020AB27
02/02/2020AC27
02/02/2020BB200
02/02/2020BB27

 

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

DateField 3Counts (Rolling dates)
01/01/20202002
 272
02/01/20202002
 271

 

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

Labels (2)
1 Solution

Accepted Solutions
Taoufiq_Zarra

ah ok

attached new version

=count(aggr(min(Date),[Field 1],[Field 2],[Field 3]))

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
Taoufiq_Zarra

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

Capture.PNG

??

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Mawkishrains
Contributor
Contributor
Author

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.

Taoufiq_Zarra

ah ok

attached new version

=count(aggr(min(Date),[Field 1],[Field 2],[Field 3]))

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.