Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_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