Discussion board where members can get started with QlikView.
I have a chart displaying marketing action performance based on two KPI : total of sales and total of discounts.
The Fact table has the following fields:
|Store ID||Receipt ID||Item ID||Sales Gross Amount||Sales Net Amount||Discount||Action ID|
The resulting chart has to be like this:
|Action ID||Total Sales||Total Discount|
The problem is with the Total Sales Net Amount, defined as follows:
=Sum(Aggr(Sum(TOTAL <[Receipt ID]> [Sales Net Amount]),[Action Id], [Receipt ID]))
it takes into account distinct items. So in the example above the result is : 23.10 which is not correct. The good value should be 31.30.
Any idea how to write the expression?
Ce message a été modifié par : Elina Demerdjieva
try with this:
=sum(aggr(sum([Sales Net Amount]),[Marketing Action ID],[Receipt ID]))
or with this:
=sum(total <[Receipt ID],[Marketing Action ID]> [Sales Net Amount])
Based on the data you provided, I would probably try to separate the discounts into a new table and link them to sales by store id, receipt id and item id (use autonumber or any other method to create composite key). This way each action id should link properly to the sales rows.
Then you should be able to get rid the aggr-expression and just use a normal sum. Seems much easier.