Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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))
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?
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
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))
Yeah! This works! Thank you so much!