21 Replies Latest reply: Nov 28, 2017 10:11 PM by Kenneth Holden

Target vs actual

Kenneth Holden Nov 28, 2017 12:03 PM

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

Re: Target vs actual

Sunny Talwar Nov 28, 2017 7:36 PM (in response to Kenneth Holden)

Which part of the expression is target?
((SUM({$<[fis year]={'2017'}>}UNITS)/Count(distinct(WEEK)))*1.10)/Sum(UNITS)/count(distinct(WEEK))
Like Show 0 Likes (0)

Actions
- Re: Target vs actual
  
  Kenneth Holden Nov 28, 2017 7:41 PM (in response to Sunny Talwar )
  
  the first half
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Target vs actual
    
    Kenneth Holden Nov 28, 2017 7:43 PM (in response to Kenneth Holden)
    
    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
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Target vs actual
      
      Kenneth Holden Nov 28, 2017 7:43 PM (in response to Kenneth Holden)
      
      from the picture, you can see I am using week number as a dimension
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 7:50 PM (in response to Kenneth Holden)
        
        i do not know if you are open to screen sharing, but I can send a zoom meeting link to share screen,
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Sunny Talwar Nov 28, 2017 8:16 PM (in response to Kenneth Holden)
        
        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)
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 8:21 PM (in response to Sunny Talwar )
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 8:22 PM (in response to Kenneth Holden)
        
        on a per week basis
        
        so week 1 differential
        week 2 differetial etc
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 8:23 PM (in response to Kenneth Holden)
        
        (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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Sunny Talwar Nov 28, 2017 8:26 PM (in response to Kenneth Holden)
        
        Don't you need just this
        
        (Sum({$<[fis year]={'2017'}>}UNITS)*1.1)/Sum({$<[fis year]={'2018'}>}UNITS)
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 8:29 PM (in response to Sunny Talwar )
        
        See what happened? 110 on all rows and I am looking for the percentage difference between target column and sum(units) column
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Sunny Talwar Nov 28, 2017 8:30 PM (in response to Kenneth Holden)
        
        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)
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 8:33 PM (in response to Sunny Talwar )
        
        will not let me
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Sunny Talwar Nov 28, 2017 8:38 PM (in response to Kenneth Holden)
        
        Two different expression Kenneth
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 8:46 PM (in response to Sunny Talwar )
        
        ((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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 9:07 PM (in response to Kenneth Holden)
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 9:08 PM (in response to Kenneth Holden)
        
        its ignoring the 2017 number which is the one i am telling it to choose
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 9:25 PM (in response to Kenneth Holden)
        
        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?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 9:27 PM (in response to Kenneth Holden)
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 10:00 PM (in response to Kenneth Holden)
        
        I have spent major hour son this what seems to be a simple thing.
        
        Qlik is choosing the 2018 values even though I have said 2017 values as outlined above.
        
        I am so frustrated as I cannot figure out why its insisting on choosing 2018 data
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Target vs actual
        
        Kenneth Holden Nov 28, 2017 10:11 PM (in response to Kenneth Holden)
        
        I figured it out.
        
        I really appreciate you being available to help though, I am sure you are busy.
        
        Thanks again.
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post