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

[Anonymous]

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

[sunny_talwar]

May be this

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

[Anonymous]

Hi Sunny,

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

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