Sum Cost per Context in relation to Target

[oldshoe]

Hello *,

I have a simple scenario but can not find a working solution.

Data:

I want Qlikview to show for dimension product how much is on/above/under target.
So expected result for car 230/40/70 and for bike 260/40/60.
I tried it with aggr in expression but it always sums over product.
That means I want to calculate on customer level and sum the results on product level.

Expression for OnTarget:

IF(SUM(Aggr(SUM(Cost_Real)<=SUM(Cost_Target),Customer,Product)),
SUM(Aggr(SUM(Cost_Real),Customer,Product)),
SUM(Aggr(SUM(Cost_Target),Customer,Product)))

Do you have any idea?

[sunny_talwar]

How are you getting these numbers? for car 230/40/70 and for bike 260/40/60 from the sample data that you have shared? can you explain which rows are getting added to get 230, 40 and 70 for cars?

[oldshoe]

Hi Sunny, thanks for your answer.

Here is the calculation for cars:

OnTarget : IF (Cost_Real <= Cost_Target , Cost_Real, Cost_Target)

= C2 + C3 + D4 = 100+30+100 = 230

AboveTarget: IF (Cost_Real > Cost_Target, Cost_Target-Cost_Real,0)

=C4-D4 = 140-100 = 40

UnderTarget: IF (Cost_Real < Cost_Target, Cost_Target-Cost_Real,0)

= D3-C3 = 100-30 = 70

[sunny_talwar · Accepted solution]

May be this

OnTarget

Sum(Aggr(
  RangeMin(Sum(Cost_Real), Sum(Cost_Target))
, Customer, Product))

AboveTarget

Sum(Aggr(
  RangeMax(Sum(Cost_Real)- Sum(Cost_Target), 0)
, Customer_Product))

UnderTarget

Sum(Aggr(
  RangeMax(Sum(Cost_Target)- Sum(Cost_Real), 0)
, Customer_Product))

[oldshoe]

Yeah! This works! Thank you so much!