Filtering by date in set analysis

shivram_l — Sat, 16 Nov 2024 00:02:27 GMT

Hello. There's a slightly convoluted problem statement that I have which I'm trying to solve. Below is a table with sample data:

date	category	merchant	type	transaction	ticket
01-01-2020	C1	M1	T1	100	5
01-01-2020	C1	M1	T2	100	10
01-01-2020	C1	M2	T3	300	15
01-01-2020	C2	M1	T4	400	20
01-01-2020	C2	M4	T5	500	25

In this, I need to use aggr functions where I need to aggregate at different levels depending on the category column. For example - If category = 'C1' then my aggr will look like:
Sum(Aggr(Max({<category = {'C1'}>} transaction), date, merchant))
For category = 'C2', it will look like:
Sum(Aggr(Max({<category = {'C2'}>} transaction), date, type))

The above function works if I apply this in a if condition, like this:

=if(category = 'C1', Sum(Aggr(Max({<category = {'C1'}>} transaction), date, merchant)),
if(category = 'C2', Sum(Aggr(Max({<category = {'C1'}>} transaction), date, type))
)
)

The problem is that I need to apply this for specific dates. I can't use these in dimensions because the logic is a bit convoluted. Eg: I am looking to get the sum(max(transactions) for yesterday and divide it by the average(sum(max(transactions))) for the last 2 weeks. So I tried something like this, which isn't giving me the output that I'm looking for.

=if(category = 'C1' and date = today()-1, Sum(Aggr(Max({<date = {'(=Date(Today() - 1))'}, category = {'C1'}>} transaction), date, merchant)),
if(payment_category = 'C2' and date = today()-1, Sum(Aggr(Max({<date = {'(=Date(Today() - 1))'}, category = {'C2'}> } transaction), date, type))
)
)

The above expression is is only for yesterday's numbers. I'm assuming that when I'm using the right expression, I'd be able to do <= date(today() - 2) and >= date(today() - 14) to get the last 2 week's values as well.

Any help is appreciated. Been struggling with this for a while now. Thanks.

Re: Filtering by date in set analysis

Brett_Bleess — Wed, 16 Sep 2020 14:45:27 GMT

Have a look at the following Design Blog post, believe that should provide some clues for you:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett

topic Re: Filtering by date in set analysis in QlikView

Filtering by date in set analysis

Re: Filtering by date in set analysis