Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ZimaBlue
Creator
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)
1 Solution

Accepted Solutions
vinieme12
Champion III
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.

View solution in original post

3 Replies
edwin
Master II
Master II

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

vinieme12
Champion III
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.
ZimaBlue
Creator
Creator
Author

it turned out to be so easy, thank you