Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikconsult
Partner - Creator
Partner - Creator

Aggr Total question

Hi

I'm looking for the correct expression in a pivot table to get the following result

image.png

I'm asuming that I have to use the aggr function and that I also have to add total to the expression, but when I try for example the following expression

=aggr(sum(total if(_year=Vyear AND accountGroupCode_mast='A' AND cat_move='MET' AND type_move='Forecast' AND accountDriver_mast='COSTING HA', value)),Fruit,Account)

i get the result

image.png

Any ideas of how I should change my expression will be greatly appreciated

Regards,

Jan-Hendrik

1 Solution

Accepted Solutions
mangalsk
Creator III
Creator III

Please find attached it is working as in sheet1

Following expression can be used

= aggr(NODISTINCT sum({<cat_move={'MET'},Account=,accountGroupCode_mast={'A'},_year={$(Vyear)},type_move={'Forecast'},cat_move={'MET'},accountDriver_mast={'COSTING HA'}>} value),Fruit)

View solution in original post

20 Replies
Anil_Babu_Samineni

Remove TOTAL keyword and show the image what you are getting?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
qlikconsult
Partner - Creator
Partner - Creator
Author

Thanks for your interest,

this is what happens when I remove the TOTAL keyword

image.png

I need however the Account field's content also to show

Anil_Babu_Samineni

Oh, Okay. try a shot something like below

=aggr(sum(TOTAL <Account, Fruit> if(_year=Vyear AND accountGroupCode_mast='A' AND cat_move='MET' AND type_move='Forecast' AND accountDriver_mast='COSTING HA', value)),Fruit,Account)


If not, Better if you provide excel or qvw ??

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
antoniotiman
Master III
Master III

Try this

Sum(aggr(sum(if(_year=Vyear AND accountGroupCode_mast='A' AND cat_move='MET' AND type_move='Forecast' AND accountDriver_mast='COSTING HA', value)),Fruit,Account))

qlikconsult
Partner - Creator
Partner - Creator
Author

Thanks for your input,

I'm however getting the next result when I try your suggestion

need to see the field Account's detail also

image.png

qlikconsult
Partner - Creator
Partner - Creator
Author

this is result from Anil;s suggetion

image.png

antoniotiman
Master III
Master III

Remove ,Account from Aggr()

qlikconsult
Partner - Creator
Partner - Creator
Author

thx, this is the result I'm getting when i remove the Account field from Aggr()

need to see all the accounts in this field however

image.png

antoniotiman
Master III
Master III

Can You provide some sample data ?