topic Re: How to remplace aggr(max()) ? in QlikView

How to remplace aggr(max()) ?

Thu, 29 Sep 2016 07:40:29 GMT

Hello everyone, I have the folowing table (example.)

Year	Month	NumTkt	PaymentMethod	Amount
2016	09	1	Money	25
2016	09	1	Cart	25
2016	09	2	Money	30
2016	09	3	Cart	45
2016	08	12	Money	20
2016	08	13	Cart	32

I'll like the sum amount for the year 2016, the month of September by ticket.

The first exepression I used is

==> sum (if (year = 2016 and month = 09, Amount))

The problem is that it returns me 50$ for Ticket 1. I want 25. So I used the following expression

==> sum (if (year = 2016 and month = 09, aggr (max (Amount), NumTkt)))

But the application become very slow, you have another solution?

Gysbert_Wassenaar — Thu, 29 Sep 2016 15:57:55 GMT

Try max(total <Year, Month, NumTkt> Amount). Or just remove the NumTkt dimension and use max(Amount) as expression.

miguelbraga — Thu, 29 Sep 2016 16:00:56 GMT

Is this what you want?

Best regards,

D.A. MB

Thu, 29 Sep 2016 16:23:54 GMT

thx for you reply,

but No , iI want 100 as result ( 25 + 30 + 45) and no 125 ( 25+25+30+45)

Year-Month	2016-09
Amount	100

Thu, 29 Sep 2016 16:27:12 GMT

sum(if(Year=2016 and Month=9 ,max(total <Year, Month, NumTkt> Amount))) return me nothin , the expression is good ?

Gysbert_Wassenaar — Thu, 29 Sep 2016 16:41:33 GMT

No, try max(total <Year, Month, NumTkt> Amount) instead of nesting it in a sum.