Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
kira_whopper
Creator
Creator

Sum of Mix Aggregated by Product Presentation

Hi there,

I need to calculate a KPI called Mix for each Product specific Presentation (every Product has one or more Presentations) and Sum the result for the Product (but still aggregated by Presentation).

The Formula is:
[ Presentation Unit Sales -
{ Product Unit Sales x

(Previous Presentation Unit Sales / Previous Product Unit Sales)
}
]

x
Previous Average Price -> Previous Presentation Sales / Previous Presentation Unit Sales

While showing the results by Presentation, I get the desired value, but when showing by Product, the result is 0

I'm sending attached a qvw file with to straight tables, one with the Presentation as a dimension (which gives the proper results for Product Mix only when Summing the rows for the total) and onde with Product as a dimension (which gives 0 as result for the Mix)

Also, I'm sending an excel worksheet with the data used in the QVW file and a tab named: "Specific Product", which has the desired result for the Mix in bold red on cell I6.

Thanks a lot, in advance...

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like attached, using advanced aggregation to calculate the sum-of-rows.

I think there should be an easier expression to calculate the same, but it seems it's too late right now for me.

View solution in original post

4 Replies
swuehl
MVP
MVP

Maybe like attached, using advanced aggregation to calculate the sum-of-rows.

I think there should be an easier expression to calculate the same, but it seems it's too late right now for me.

sunny_talwar

Try this:

Sum(Aggr((Sum([Unit Sales]) - (Sum(TOTAL <Product>[Unit Sales]) * (Sum([Previous Unit Sales])/Sum(TOTAL <Product> [Previous Unit Sales])))) * (Sum([Previous Sales])/Sum([Previous Unit Sales])), Product, Presentation))

kira_whopper
Creator
Creator
Author

Thanks a lot! That's it!

kira_whopper
Creator
Creator
Author

Thanks too, also correct!