Issues with 'sum of rows' / expression default summation

paul_ripley — Wed, 31 Oct 2018 17:24:15 GMT

Hello

I have a table below, using sum of rows

When I change it to 'expression total' I get £16,732,021.

I actually just want one line in the table 'Total', so I remove the dimension 'team_description' and enter the calculated dimension showing 'Total'

and the number changes to below as though I am using 'expression default' as the summation

How can I replicate 'sum of rows' in my expression. I think it has something to do with aggr and sum? But it doesn't seem to work for me. I attach my expression below - but as you can see it is quite long!

Any help would be appreciated

Thanks

Paul

=If(
sum(aggr(((sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description -= {'P&P','IT'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'P&P'},bud2 -= {200,260}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'IT'}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))
)
* PE_Alloc%),team_description)) <> 0,

/////////////////////////////////////////////////////////////////////////////////

((sum(aggr(((sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description -= {'P&P','IT'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'P&P'},bud2 -= {200,260}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'IT'}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))
)
* PE_Alloc%),team_description))
)
/
(sum(aggr(((sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description -= {'P&P','IT'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'P&P'},bud2 -= {200,260}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'IT'}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))
)
* PE_Alloc%),team_description))

+

sum(aggr(((sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description -= {'P&P','IT'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'P&P'},bud2 -= {200,260}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'IT'}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))
)
* Infra_Alloc%),team_description))
)
)

*

sum(aggr(((sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description -= {'P&P','IT'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'P&P'},bud2 -= {200,260}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'IT'}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))
)
* PC_Alloc%),team_description))

,

////////////////////////////////////////

sum(aggr(((sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description -= {'P&P','IT'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'P&P'},bud2 -= {200,260}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'IT'}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))
)
* PC_Alloc%),team_description))*PE_PC_SPLIT

)
////////////////////////////////

+
sum(aggr(((sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description -= {'P&P','IT'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'P&P'},bud2 -= {200,260}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))

+

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {">0"},
team_description = {'IT'}, bud2_compen2 -={'Office Costs'},
Datasource = {'Actual'}, busarea = {PC}
>} $(vCurrency))
)
* PE_Alloc%),team_description))

Re: Issues with 'sum of rows' / expression default summation

chrismarlow — Wed, 31 Oct 2018 17:57:19 GMT

Paul,

My immediate thought is that if your expression is that long then you probably need to do something in the script to tag up your data to make it simpler to define your sets/do your calculation. From that point possibly then easier to decode how to get the summation right, or it may drop away as an issue.

Is that an option?

Cheers,

Chris.

Re: Issues with 'sum of rows' / expression default summation

andrey_krylov — Wed, 31 Oct 2018 18:04:02 GMT

Hi Paul. Sum(Aggr(....), ListOfDimensions) replicates the sum of rows but you have to aggregate the entire expression and not individual parts

Re: Issues with 'sum of rows' / expression default summation

mikevwang — Thu, 01 Nov 2018 05:41:49 GMT

A quick fix is probably to wrap your entire expression in a

SUM(

AGGR(

//your expression

,team_description

)

When you do a SUM(AGGR(expression,team_description)), Qlik is creating a table behind the scenes with team_description as dimension and expression as the result, and then the outer SUM() will add them up according to your table dimensions. This is basically what you are doing by doing a sum of rows while having team_description as a dimension.

That said, your inner expression also has a lot of AGGRs on team_description as well, so it might be worth cleaning that up. You probably won't need to have nested AGGRs now that you know what it's doing behind the scenes, and without the nested AGGRs, you probably won't need to keep repeating the same 3 set analysis sums 7 times.

Re: Issues with 'sum of rows' / expression default summation

paul_ripley — Thu, 01 Nov 2018 11:34:24 GMT

Thanks all for your help.

The expression is really messy but when I used the format below as mentioned by Mike it worked.

Many thanks for all your help

SUM(

AGGR(

//your expression

,team_description

)

topic Re: Issues with 'sum of rows' / expression default summation in QlikView

Issues with 'sum of rows' / expression default summation

Re: Issues with 'sum of rows' / expression default summation

Re: Issues with 'sum of rows' / expression default summation

Re: Issues with 'sum of rows' / expression default summation

Re: Issues with 'sum of rows' / expression default summation