topic Re: Set analysis: sum aggr in QlikView

Set analysis: sum aggr

paul_ripley — Thu, 30 Aug 2018 15:39:10 GMT

Hello

I have a chart shown below. I would like to get some set analysis code that I can use in my N Printing report for the totals , eg 772,717 and 554,105 and 901,604

The expression eg for the PE column is

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
costc = {681,685},
bud2_line = {"OFFICE COSTS"},
Datasource = {'Actual'}
>} $(vCurrency)) * PE%

PE% is a percentage held in a spreadsheet that I load into QV

When I take this code and put it into a text box it doesn't work - it brings back zero, even though it works in the table.

I wondered if it is anything to do with aggregating by city_map

I tried the below but this although it brings back a number it brings back the wrong number - can you suggest what I am doing wrong?

=Sum(Aggr(
sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
bud2 = {"200"},
costc = {681,685},
bud2_line = {"OFFICE COSTS"},
Datasource = {'Actual'}
>} $(vCurrency))
* PE%
, city_map))

Many thanks

Paul

Re: Set analysis: sum aggr

sunny_talwar — Thu, 30 Aug 2018 15:41:52 GMT

Try by adding the inner aggregation's set analysis to the outer aggregation

=Sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, bud2 = {"200"}, costc = {681,685}, bud2_line = {"OFFICE COSTS"}, Datasource = {'Actual'}>} Aggr(Sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, bud2 = {"200"}, costc = {681,685}, bud2_line = {"OFFICE COSTS"}, Datasource = {'Actual'}>} $(vCurrency)) * PE%, city_map))

Re: Set analysis: sum aggr

paul_ripley — Thu, 30 Aug 2018 15:51:02 GMT

Thanks Sunny

That gave me the same answer as I was getting.

If I use this code, ie take off the final *PE% (and for the other columns it was *INF% and *PC% then I get the total of the 3 columns, which would be correct

But when I use the *PE% in a text field I get the wrong result in the PE column (and the Infra/PC columns)

sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
costc = {681,685},
bud2_line = {"OFFICE COSTS"},
Datasource = {'Actual'}
>} $(vCurrency))

Re: Set analysis: sum aggr

paul_ripley — Thu, 30 Aug 2018 15:54:33 GMT

..just to add each city_map has a different %ge that it is multiplied by ie that's why I thought you would aggregate somehow

Re: Set analysis: sum aggr

sunny_talwar — Thu, 30 Aug 2018 16:14:35 GMT

There are two dimensions in the chart? Add both to your Aggr() function

Re: Set analysis: sum aggr

paul_ripley — Thu, 30 Aug 2018 16:25:38 GMT

Thanks Sunny

Not sure why this now works but this worked for me

=Sum(Aggr(
sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},
costc = {681,685},
bud2_line = {"OFFICE COSTS"},
Datasource = {'Actual'}
>} $(vCurrency)) * PE%
, PE%))