Problem with the aggr sum in the pivot table

ZimaBlue — Fri, 10 Jun 2022 21:43:49 GMT

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!

Re: Problem with the aggr sum in the pivot table

edwin — Sat, 11 Jun 2022 15:16:26 GMT

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

Re: Problem with the aggr sum in the pivot table

vinieme12 — Mon, 13 Jun 2022 04:25:02 GMT

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

Re: Problem with the aggr sum in the pivot table

ZimaBlue — Tue, 14 Jun 2022 12:29:42 GMT

it turned out to be so easy, thank you

topic Re: Problem with the aggr sum in the pivot table in App Development

Problem with the aggr sum in the pivot table

Re: Problem with the aggr sum in the pivot table

Re: Problem with the aggr sum in the pivot table

Re: Problem with the aggr sum in the pivot table