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

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

zarmoton — Tue, 12 May 2020 14:04:57 GMT

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

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

sunny_talwar — Tue, 12 May 2020 15:44:08 GMT

May be this

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

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

zarmoton — Tue, 12 May 2020 16:09:09 GMT

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 !!

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

sunny_talwar — Tue, 12 May 2020 16:18:19 GMT

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

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

zarmoton — Wed, 13 May 2020 13:17:43 GMT

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

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

sunny_talwar — Wed, 13 May 2020 13:38:15 GMT

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

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

Brett_Bleess — Thu, 04 Jun 2020 18:39:04 GMT

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-performance-optimization.htm

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/LoadData/best-practices-data-modeling.htm

Regards,
Brett

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

zarmoton — Mon, 29 Jun 2020 07:37:31 GMT

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

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

sunny_talwar — Tue, 30 Jun 2020 18:11:26 GMT

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