Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
zarmoton
Creator
Creator

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

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:

 

zarmoton_0-1589292269999.pngzarmoton_1-1589292285780.png

 

HELP me please, I really appreciate any help

 

here are my data:

MyDateSite NumberPromotion CodeTYPE_GRADEAMOUNT
01/02/20202130
01/02/2020226-12
01/02/2020226-6
01/02/20202333
05/02/202011010
05/02/2020120710
05/02/202012175
05/02/20201301-3
06/02/2020350112
06/02/2020375317
06/02/2020370111
06/02/20203803-10

regards

1 Solution

Accepted Solutions
sunny_talwar

May be this

 

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

 

View solution in original post

8 Replies
sunny_talwar

May be this

 

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

 

View solution in original post

zarmoton
Creator
Creator
Author

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
Creator
Creator
Author

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
zarmoton
Creator
Creator
Author

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?