topic Sum Cost per Context in relation to Target in QlikView

Sum Cost per Context in relation to Target

oldshoe — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Sum Cost per Context in relation to Target

sunny_talwar — Mon, 11 Nov 2019 13:25:50 GMT

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?

Re: Sum Cost per Context in relation to Target

oldshoe — Mon, 11 Nov 2019 14:26:31 GMT

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

Re: Sum Cost per Context in relation to Target

sunny_talwar — Mon, 11 Nov 2019 16:09:50 GMT

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

Re: Sum Cost per Context in relation to Target

oldshoe — Tue, 12 Nov 2019 09:21:10 GMT

Yeah! This works! Thank you so much!