Expanding Ranges in Expression.

[Mawkishrains]

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

[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

??

[Mawkishrains]

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 · Accepted solution]

ah ok

attached new version

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

[Brett_Bleess]

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