Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wim_rijken
Partner - Contributor II
Partner - Contributor II

Target vs Actual percentage

I want to show target vs actual in a pivot table.

I have 2 tables: 1 with actuals, 1 with the targets (target margin %, target amount)

I combine these in a fact-table.

[

TargetActual

Product

Amount

Cost

Target margin pct

]

Now I want to show Actual and KPI next to each other based on the dimension "TargetActual" in 1 expression

The problem is that the calculation for the actual margin (amount-cost)/amount is NULL.

So it does not show a pct value.

see example attached.

What is the solution for this?

1 Solution

Accepted Solutions
sunny_talwar

Try using RangeSum, instead of + to sum the two expressions

RangeSum(

(

//Margin of Actual

(sum({$<TargetActual={'Actual'}>}Amount) - sum({$<TargetActual={'Actual'}>}Cost)) / sum({$<TargetActual={'Actual'}>}Amount)

)

,

(

//Target margin

sum({$<TargetActual={'Target'}>}[Target margin pct])/100

))

View solution in original post

1 Reply
sunny_talwar

Try using RangeSum, instead of + to sum the two expressions

RangeSum(

(

//Margin of Actual

(sum({$<TargetActual={'Actual'}>}Amount) - sum({$<TargetActual={'Actual'}>}Cost)) / sum({$<TargetActual={'Actual'}>}Amount)

)

,

(

//Target margin

sum({$<TargetActual={'Target'}>}[Target margin pct])/100

))