topic Re: How to aggregate an expression based on dimensions in a pivot or straight table in QlikView

How to aggregate an expression based on dimensions in a pivot or straight table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm new in using QlikView and I am not able to figure out a problem that I supposed pretty simple to solve (at least I hope it).

Following my scenario: I have a table with an expression (MPE_w1) linked to other ones and I need to sum the rows of this column based on dimensions of 'Month' and 'Name'.

I tried to calculate this quantity using this expression: sum(aggr(sum(MPE_w1),Name,Month)) but the result is equal to 0

Attached you will find an excel file with an example of my data.

Thanks in advance for the help

Re: How aggregate an expression based on dimensions in a pivot or streight table

sunny_talwar — Thu, 21 Jun 2018 17:12:26 GMT

May be this

Sum(TOTAL <Name, Month, Year, Quarter> Aggr($(w1)/Sum(TOTAL <Name, Month, Year, Quarter> $(w1))*MPE, ItemId, Year, Quarter, Month, Name))

Re: How aggregate an expression based on dimensions in a pivot or streight table

Anonymous — Fri, 22 Jun 2018 07:08:20 GMT

Hi Sunny,

I've just tried your solution but the result still is zero for the whole column

Re: How to aggregate an expression based on dimensions in a pivot or straight table

sunny_talwar — Fri, 22 Jun 2018 16:02:55 GMT

Would you be able to share a qvw to test and play around with?

Re: How to aggregate an expression based on dimensions in a pivot or straight table

Anonymous — Mon, 25 Jun 2018 09:34:12 GMT

Attached you will find the qvw file with both pivot and straight tables, thanks in advance.

Re: How to aggregate an expression based on dimensions in a pivot or straight table

sunny_talwar — Mon, 25 Jun 2018 15:25:55 GMT

This seems to have worked... had to make few changes in the script though

sum(TOTAL <Name, Month, Year, Quarter>Aggr($(w1)/Sum(TOTAL <Name,Month,Year,Quarter> $(w1))*(Aggr(RangeSum(Above(if(IsNull($(E%M)),10,$(E%M)), 0, RowNo())), ItemId, Name, YearMonth)/Aggr(RowNo(), ItemId, Name, YearMonth)), ItemId, Year, Quarter, Month, Name))

Re: How to aggregate an expression based on dimensions in a pivot or straight table

Anonymous — Mon, 25 Jun 2018 16:12:29 GMT

Perfect, I will study your project to understand how it works, but in any case the output is what I was looking for. Thank you!!!