SUM in a sub selection in Chart-Pivot Table with MAX function => a Riddle !

[zarmoton]

Hi 

Probably simple, but a new in qlik view, i can't find the issue

in the last column, i need to have a sum of amount , where i take only the row (by MyDate, Site_Number) where TYPE_GRADE is the Max value

My expression is this:

=Sum({ $< TYPE_GRADE = {"=aggr(NODISTINCT max(TYPE_GRADE), MyDate, [Site Number])=TYPE_GRADE" }> } TOTAL <MyDate, [Site Number]> AMOUNT)

can you understand why when I do a selection it's working, but without selection not working:

HELP me please, I really appreciate any help

here are my data:

MyDate	Site Number	Promotion Code	TYPE_GRADE	AMOUNT
01/02/2020	2	1	3	0
01/02/2020	2	2	6	-12
01/02/2020	2	2	6	-6
01/02/2020	2	3	3	3
05/02/2020	1	10	1	0
05/02/2020	1	20	7	10
05/02/2020	1	21	7	5
05/02/2020	1	30	1	-3
06/02/2020	3	50	1	12
06/02/2020	3	75	3	17
06/02/2020	3	70	1	11
06/02/2020	3	80	3	-10

regards

[sunny_talwar · Accepted solution]

May be this

=FirstSortedValue(TOTAL <MyDate, [Site Number]> Aggr(
     Sum(AMOUNT)
, MyDate, [Site Number], TYPE_GRADE), -Aggr(TYPE_GRADE, MyDate, [Site Number], TYPE_GRADE))

[zarmoton]

YYYEEEEESSSS !

You cannot imagine how i'm happy !!! great looks perfect !

Can you explain the construction of this expression 

i don't understand it at all !!

[sunny_talwar]

What do you not understand? If you need help with FirstSortedValue function

[zarmoton]

expression is working pretty well.

Except that performance is not very good. 

It take a long time to compute (around 100000 rows)

any idea to optimize it ?

regards

[sunny_talwar]

I don't think it can be, but I might be wrong. May be someone else can help you out.

[Brett_Bleess]

Couple of links related to the performance side of things you will likely find helpful:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/application-per...

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/LoadData/best-p...

Regards,
Brett

[zarmoton]

Hi 

one more time thank you Sunny,

But i'm facing to performance issue, it's really too slow (because my table is huge). Maybe should be better to calculate not in an expression inside the pivot table, but in the script load.

Like that in pivot table it will be immediate.

Any idea how to do it ?

Thk yu

[sunny_talwar]

Doing it in the script will make it static and values won't change based on selection. Is that what you want?