Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kennethholden
Contributor

Target vs actual

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

Screenshot of Safari (2017-11-28, 9-59-29 AM).png

Tags (1)
21 Replies
MVP
MVP

Re: Target vs actual

Which part of the expression is target?

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

kennethholden
Contributor

Re: Target vs actual

the first half

kennethholden
Contributor

Re: Target vs actual

I took sum of units from 2017 fiscal and multiplied it by 1.10 to give a target of a 10% increase per weekly average, now I am trying to take first four weeks of fiscal 2018(first four weeks of November) and measure that against the target

kennethholden
Contributor

Re: Target vs actual

from the picture, you can see I am using week number as a dimension

kennethholden
Contributor

Re: Target vs actual

i do not know if you are open to screen sharing, but I can send a zoom meeting link to share screen,

MVP
MVP

Re: Target vs actual

What are the numbers you are expecting to see? May be try this

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

kennethholden
Contributor

Re: Target vs actual

ya not working

example:

2017 week 1 - 50 units

goal for 2018 weel 1 50 * 1.10 or 55

actual 2018 was 40 so looking for -23.7% less

I am looking for that -23.7% differential between target which is 55 and actual which was 40

kennethholden
Contributor

Re: Target vs actual

on a per week basis

so week 1 differential

week 2 differetial etc

kennethholden
Contributor

Re: Target vs actual

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

this is the expression for the weekly target column and it works perfectly

now I am trying to take the current year and divide by the above expression