In the rolling numbers, how to get the latest one?

[harrywu1]

Hi experts,

I have a set of rolling FCST numbers, where the current month(N) forecasts "the current month and the next few months til the end of year" (N til End of year).  That means, for example, when we are at the beginning of May2017, we will have a new version of forecast for May2017 to Dec2017.

Data composition is plotted as below, and data has been transformed into Fields 'VERSION' & 'PERIOD' :

VERSION = {PERIODs}

For example: VERSION '201707' = {PERIOD: 201707,201708,201709,201710,201711,201712}

However, problem is how do I get the latest FCST number of a certain month?

"The latest FCST of certain month" means, for example, May's latest FCST is the number in Ver. May = 277, and 263 as Sep's latest FCST number, i.e. the yellow-filled cells.

Please ignore using MAX or MIN functions, since I am not sure the blanks are blank in data... VERSION 201707 might have {0,0,0,0,0,0,7,8,9,10,11,12}...

I tried SUM({$<VERSION = {PERIOD}>} FCST_AMT) , but returned zeros..

Need your help!  Thanks!

[Anonymous]

Harry,

what will be the expected out put screen\object?, where you want to see the value?

[harrywu1]

Hi John,

Thanks for asking the detail!

The output could be at the format of Pivot-Table or Straight-Table, with one column as Dimension (201701-201712) and the other column as the desired numbers.

[agomes1971]

Hi,

please read this:

Set Analysis for Rolling Periods

HTH

André Gomes