topic Re: Expanding Ranges in Expression. in QlikView

Expanding Ranges in Expression.

Mawkishrains — Sat, 16 Nov 2024 19:05:37 GMT

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

Re: Expanding Ranges in Expression.

Taoufiq_Zarra — Wed, 19 Feb 2020 10:24:17 GMT

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

Re: Expanding Ranges in Expression.

Mawkishrains — Wed, 19 Feb 2020 12:38:19 GMT

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.

Re: Expanding Ranges in Expression.

Taoufiq_Zarra — Wed, 19 Feb 2020 12:51:39 GMT

ah ok

attached new version

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

Re: Expanding Ranges in Expression.

Brett_Bleess — Tue, 24 Mar 2020 19:25:13 GMT

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