Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
n1ef5ng1
Creator
Creator

YOY - Prevent qv to substract year that has no value

I have five year data, from 2008 - 2012

Bascially I am currently doing YOY on these data. I need to set Year_Month as my dimension as I need to extrapolate my latest incomplete month value.

Is there any codes that I am able to use to prevent 2008 to substract 2007 as there is no data at all?

One of the code is

=if( Before( Sum (IF(Derived_Status <> 'LRSHP', TEU,0)) )  = 0

  OR IsNull( Before( Sum (IF(Derived_Status <> 'LRSHP', TEU,0)) )) = -1, null(),

But i am unable to use this as Before will list out all the value in the dimension even if it is null or empty value.

This is my code for now

=(if( MonthEnd ( MAX( Date ) ) <> var_EndDate AND MonthEnd( MAX ( Date ) )
= MonthEnd( var_EndDate ) ,

(

  (sum({$<Year = {$(v_ThisYear)},Unlocode_Port_Of_Loading = {'ID*'}>} TEU))

-


  (sum({$<Year = {$(v_LastYear)},Unlocode_Port_Of_Loading = {'ID*'}>} TEU))
  / Day( var_EndDate )) * Day(MonthEnd(MAX ( Date )))
)
,

(

  (sum({$<Year = {$(v_ThisYear)},Unlocode_Port_Of_Loading = {'ID*'}>} TEU))

-


  (sum({$<Year = {$(v_LastYear)},Unlocode_Port_Of_Loading = {'ID*'}>} TEU))

)

))

Basicially the first line of code check whether is it the latest month, if it is, extrapolate the value.

Any help is appreciated as i m stuck for the whole day, thanks

0 Replies