Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
giuseppecarcion
Contributor
Contributor

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

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

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

giuseppecarcion
Contributor
Contributor
Author

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?

giuseppecarcion
Contributor
Contributor
Author

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

sunny_talwar

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

Capture.PNG

View solution in original post

giuseppecarcion
Contributor
Contributor
Author

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