Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm looking for the correct expression in a pivot table to get the following result
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
Any ideas of how I should change my expression will be greatly appreciated
Regards,
Jan-Hendrik
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)
Remove TOTAL keyword and show the image what you are getting?
Thanks for your interest,
this is what happens when I remove the TOTAL keyword
I need however the Account field's content also to show
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 ??
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))
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
this is result from Anil;s suggetion
Remove ,Account from Aggr()
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
Can You provide some sample data ?