topic Re: Using filters while using a TOTAL statement in QlikView

Using filters while using a TOTAL statement

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

There's a table that is created that has 5 columns:

In the above table, Tickets is unique for a combination of Date-Category-Merchant-Type while Transaction is unique for a combination of Date-Category-Merchant (Meaning there will be duplicate values)

One of the metric we need is Ticket/Transactions. In this case, ticket is additive so we just do a sum. For transactions, I use the below statement:

sum(total <date, merchant> if(category = 'category1', aggr(max(transactions), cs_date, merchant)))

The above statement helps in getting the distinct transactions for a date-merchant combination which is then summed to get total transactions.

The above example is working in terms of the intended effect, but we came across a certain edge case where the logic is failing.

Date	Category	Merchant	Type	Transaction	Ticket
01-Jan	C1	M1	T1	100	5
01-Jan	C1	M1	T2	100	10
01-Jan	C1	M2	T3	300	15
01-Jan	C2	M1	T4	400	20
01-Jan	C2	M4	T5	500	25

In the above example, ideally for C1 - M1, the number of transaction based on the above TOTAL statement should be 100 and for C2 - M1 it should be 400

But I'm getting the total for C1 - M1 to be 400 and C2 - M1 to also be 400.

Beginner to Set and Total statements - so any help is appreciated. Thanks.

Re: Using filters while using a TOTAL statement

sunny_talwar — Mon, 31 Aug 2020 12:53:36 GMT

Have you just tried this

Sum(Aggr(Max(transactions), cs_date, merchant))

Why do you need the if statement? I am guessing category1 = C1 and category2 = C2.... If that is true and you want to see a number for C2-M1 combination, why would you need an if statement? Also, guessing cs_date = Date?

I am not sure if the above expression serves the purpose or not, but it would be easier if you can provide a better example where sample data and the field names in your expression match because right now we have some guess work to do.

Re: Using filters while using a TOTAL statement

shivram_l — Mon, 31 Aug 2020 13:11:10 GMT

Yeah. I can understand why the confusion might be there. Pasting the right table and query below for reference:

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

sum(total <date, merchant> if(category = 'C1', aggr(max(transaction), date, merchant)))

So let me try and explain why I need to have an if condition. In my dashboard, I need to have one view for each separate category. Tab-1 will be called C1 where the above statement and other such expressions will have to be only for "C1". Similar for C2, C3, and so on...

@sunny_talwar - I tried running the query that you shared. Facing the same problem where for C1-M1 combination I get 400, instead of 100.

This is the expression that I used

sum(if(category = 'C1' , Aggr(Max(transaction), date, merchant)))

Re: Using filters while using a TOTAL statement

sunny_talwar — Mon, 31 Aug 2020 13:31:43 GMT

Try this

Sum(Aggr( Max({<category = {'C1'}>} transactions) , cs_date, merchant))

Re: Using filters while using a TOTAL statement

shivram_l — Mon, 31 Aug 2020 15:55:32 GMT

Thanks, Sunny. Just out of curiosity. Will the same formula work if I need to do a date based filter? Something like today() - 1 or today() - 7?

Re: Using filters while using a TOTAL statement

sunny_talwar — Mon, 31 Aug 2020 15:58:41 GMT

It should, just make sure to use correct date format when comparing date to Today() - 1 or Today()-7 using the Date function

Re: Using filters while using a TOTAL statement

shivram_l — Tue, 01 Sep 2020 12:21:25 GMT

Hey @sunny_talwar - So I am trying to use the base solution that you'd suggested above and was trying to expand that further to the need that I have in the dashboard.

So, here are some of the nuances:

- The variables that I'm trying to group by in the aggr() function is dependent on the category. So for category = 'C1', might have to aggr based on date and merchant, but for category = 'C2', might have to aggr based only on date.
In this case, a nested If might work is what I'm thinking

- There's also a date filter I need to apply. Can't use this in the dimension since for the same metric I need to take yesterday's value and benchmark against the last 2 weeks' value. I tried the below expression but it doesn't seem to be right.

Sum(Aggr(
Max({<category = {'C1'} and date = {today() - 2} >} transactions)
, cs_date, merchant))