advanced calculations for the Budget Availability

[oleggggggM]

Hello.

I have the following task which I don't know how to address.

There is a list of orders with Budget and Actual spending.  I am trying to build the KPI that will show total remaining budget

Remaining budget = Original budget - Actual spending. 

Sounds simple as you just build a formula where: Sum(Budget) - Sum(Actual) 

However, I do have a few orders with overrun/overspending. So when Bud - Act < 0, this order does not need to be considered for the Remaining Budget KPI calculation as it is with minus and so affects the total number.

Example:

Order | Budget | Actual

1xxx | 10,000 | 9,000

2xxx | 20,000 | 23,000

The Total Remaining Budget amount should be 1,000 from order 1xxx, as order 2xxx has overrun and no budget is left.

With the standard approach, the above example will end up in the Remaining Budget KPI at -2,000 (10,000 - 9,000= 1,00 and 20,000 - 23,000 = -3,000. Total = 1,000 - 3,000 = -2,000) which is incorrect. 

Any suggestions how the KPI formula should look like? thank you so much for any reference or advice. 

[Or]

Sum(RangeMax(Budget-Actual,0)) should work here, if the example data matches the actual data.

[oleggggggM]

Not exactly. My example was too simplified maybe, but in reality, I have several line items for the actual amount:

Order | Budget | Actual

1xxx | 10,000 | 5,000

1xxx |                | 4,000

2xxx | 20,000 | 23,000

something like that. So I have to use Sum(Amount). I do have some other set analysis within that SUMs but that is skipped for now to be able to explain. Any other suggestions?

[Or · Accepted solution]

Same concept with aggr(), I suppose. Syntax should look something like:

Sum(RangeMax(0,aggr(sum(Budget)-Sum(Actual),Order)))

[oleggggggM]

Perfect. Works well, although I do not understand why 🙂

Can you please, elaborate on the logic for this expression? I tried to read QlikHelp around RangeMax but still don't follow/understand exactly the logic for the solution above. Many thanks in advance for your time.

p.s. The above post will be marked as "accepted solution"

[Or]

RangeMax just picks out the highest value from the ones it's given, e.g. RangeMax(1,3,2) will pick out 3. In your case, we wanted to avoid negative values, so RangeMax(NegativeValue,0) will pick out 0 but RangeMax(PositiveValue,0) will pick out the positive value every time.

For the aggr(), we create a subset of sum(Budget)-Sum(Actual) for each order, and then for each one of those we check if that value is greater than 0 using the RangeMax(). In this case I think the RangeMax() would work both inside the aggr() and outside of it.