Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
kira_whopper
Contributor

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

Re: Sum of Mix Aggregated by Product Presentation

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.

4 Replies
MVP
MVP

Re: Sum of Mix Aggregated by Product Presentation

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.

Re: Sum of Mix Aggregated by Product Presentation

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
Contributor

Re: Sum of Mix Aggregated by Product Presentation

Thanks a lot! That's it!

kira_whopper
Contributor

Re: Sum of Mix Aggregated by Product Presentation

Thanks too, also correct!