# App Development

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Creator

## Problem with the aggr sum in the pivot table

Hello!

I use measure formula in the pivot table and i get problem each time (tried three different data loading logic).

I will try to give a simplified example here. My pivot table looks like this.

 keyword (dimension) expenditure (measure) key1 1000 key2 5000

Where the measure column is the following formula:

SUM({1<exp_date={">=\$(vDateFrom)<=\$(vDateTo)"}>} AGGR(SUM({1 <exp_date={">=\$(vDateFrom)<=\$(vDateTo)"}, keyword=P(keyword)>}
DISTINCT spent),
exp_aggr_field))

Since there are duplicate rows in the original data array, i created a field for aggregation 'exp_aggr_field', which consists 'date-partner-keyword'. I use this field to calculate the amount of expenses.

When I look at the data array and manually match the rows, then I get the value 3000 for key1. I am reliably aware that this is the correct value and the data array looks correct

In the pivot table, I see a value 1000 if no filters are applied. If apply a filter on a dimension from the pivot table (keyword), the value turns into 3000 - correct. Problem occurs only for some rows. Is this a pivot bug of the old version of qliksense (our company uses the 2017 version) or is there something wrong with the logic of the formula? In that case, why doesn't it work only for some rows?

I want to get the same value for the keyword row, regardless of whether filters are applied or not.

Thanks for any tips!

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Champion III

add the actual dimension field in Aggr()

=SUM({1<exp_date={">=\$(vDateFrom)<=\$(vDateTo)"}>} AGGR(SUM({1 <exp_date={">=\$(vDateFrom)<=\$(vDateTo)"}>}
DISTINCT spent),
exp_aggr_field, Keyword))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
3 Replies
Master

the problem isnt very clear, maybe you would want to post sample data and the expected result.

Champion III

add the actual dimension field in Aggr()

=SUM({1<exp_date={">=\$(vDateFrom)<=\$(vDateTo)"}>} AGGR(SUM({1 <exp_date={">=\$(vDateFrom)<=\$(vDateTo)"}>}
DISTINCT spent),
exp_aggr_field, Keyword))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Creator
Author

it turned out to be so easy, thank you

Tags
Community Browser