topic Re: Aggr Total question in QlikView

Aggr Total question

qlikconsult — Thu, 20 Jul 2017 07:34:58 GMT

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

Re: Aggr Total question

Anil_Babu_Samineni — Thu, 20 Jul 2017 07:38:42 GMT

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

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 07:43:05 GMT

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

Re: Aggr Total question

Anil_Babu_Samineni — Thu, 20 Jul 2017 07:48:28 GMT

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

Re: Aggr Total question

antoniotiman — Thu, 20 Jul 2017 07:51:41 GMT

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

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 07:56:04 GMT

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

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 07:59:20 GMT

this is result from Anil;s suggetion

Re: Aggr Total question

antoniotiman — Thu, 20 Jul 2017 08:00:20 GMT

Remove ,Account from Aggr()

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 08:05:45 GMT

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

Re: Aggr Total question

antoniotiman — Thu, 20 Jul 2017 08:08:55 GMT

Can You provide some sample data ?

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 08:42:39 GMT

hi, yes thx, I will compile the qvw and post ASAP

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 09:17:18 GMT

Hi, I have attached the .qvw on my original post,

what makes it a bit complicated is the fact that I'm getting the values by filtering on the field cat_move = 'MET' but I still want to show it aggregated on total by fruit for the different Account lines coming from cat_move = 'FIN'

Re: Aggr Total question

antoniotiman — Thu, 20 Jul 2017 09:28:11 GMT

But Accounts are Null.

What are Dimensions and Expression formulas in chart ?

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 09:37:19 GMT

I have attached my .qvw on the first post (scroll up to beginning)

The reason why the account field is null for cat_move = 'MET' is because it is a metrics value which apply to the whole 2017 year while the records cat_move = 'FIN' are financial values which are posted in different periods (thus not annual)

The reason why they are in the same table is because I also need to calculate financials devided by metrics in other qlikview objects (in this example I only want the metrics, but totaling over the account lines from the financials data set

Do you think it is possible to do?

Re: Aggr Total question

PrashantSangle — Thu, 20 Jul 2017 09:42:18 GMT

Hi,

your second object is table box it will show all data which you are loaded.

In first chart try to use set analysis.

Regards,

Re: Aggr Total question

mangalsk — Thu, 20 Jul 2017 09:43:33 GMT

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)

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 09:46:41 GMT

great, thanks Mangal, that is what I'm looking for

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 09:47:41 GMT

thanks Antonio, Mangal gave me an expression I could use

Re: Aggr Total question

PrashantSangle — Thu, 20 Jul 2017 09:52:57 GMT

What you want to show in chart???

the value which have cat_mov = 'MET' or cat_mov = 'FIN'

Try below expression

sum({<accountGroupCode_mast={"A"},type_move={"Forecast"},accountDriver_mast={"COSTING HA"},_year={"$(Vyear)"}>}value)

Regards,

Re: Aggr Total question

qlikconsult — Thu, 20 Jul 2017 10:13:08 GMT

thanks, Mangal assisted me with correct answer