Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
paul_ripley
Creator III
Creator III

Set analysis: sum aggr

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

5 Replies
sunny_talwar

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

paul_ripley
Creator III
Creator III
Author

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)

paul_ripley
Creator III
Creator III
Author

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

sunny_talwar

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

paul_ripley
Creator III
Creator III
Author

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