Target vs actual

[Anonymous]

((SUM({$<[fis year]={'2017'}>}UNITS)/Count(distinct(WEEK)))*1.10)/Sum(UNITS)/count(distinct(WEEK))

I am attempting to measure the percentage difference between the weekly average for units from current fiscal year  against the target average which is 2017 fiscal year and the growth set at 10%, so hence 1.10

I end up constantly with the 1.10 as the answer.

The target calculation works fine as you can see, but the formula above for measure difference between current fiscal year sum(units)/count(distinct(week) and the target always comes out wrong.

I am fairly new still to qliksense and learning fast but some assistance, I am sure the answer is obvious but I do not see it.

Thanks

[sunny_talwar]

Don't you need just this

(Sum({$<[fis year]={'2017'}>}UNITS)*1.1)/Sum({$<[fis year]={'2018'}>}UNITS)

[Anonymous]

See what happened? 110 on all rows and I am looking for the percentage difference between target column and sum(units) column

[sunny_talwar]

Create two expressions in the same chart and share a screenshot of there result

1) Sum({$<[fis year]={'2017'}>}UNITS)

2) Sum({$<[fis year]={'2018'}>}UNITS)

[Anonymous]

will not let me

[sunny_talwar]

Two different expression Kenneth

[Anonymous]

((Sum(UNITS)/(Sum({$<[FIS YEAR]={'2017'}>}UNITS))*1.1))-1

i did this while waiting and discovered a problem. The way I did this it always shows teh exact 10% gap.........so what I need to tell Qlik is take each week of fiscal 2017 and add 10% to it for the target, and then then measure teh current year actual against the fixed total

[Anonymous]

I have a problem when calculating the target, it is multiplying the 2018 number by 10% vs 2017

both expressions you have above is what I already have for the first two columns, and it shows exact results as in the picture above.

For some reason even though I Have $ fis year 2017, it  is creating the target number off of the 2018 number......very weird it

this is my expressiion to calculate the target number

(SUM({$<[fis year]={'2017'}>}UNITS))*1.10

it is still mutiplying off of teh 2018 number vs 2017

[Anonymous]

its ignoring the 2017 number which is the one i am telling it to choose

[Anonymous]

so my original expression for the difference between target and 2018 actual was working. 🙂

the issue is the new one above, Qlik is calculating the target off of the 2018 actual vs the 2017 number

am I missing some operator in that expression? for target?

[Anonymous]

if you multiply the values in 2018 column by 10% you get the new value in target column, this should be the value of 10% off the 2017 column

I am somewhat certain the expression I wrote for the target column was accurate