Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

harrywu1
New Contributor III

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

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}

QV_FCST.PNG

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!

Tags (1)
3 Replies
qlik_ashok
New Contributor III

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

Harry,

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

harrywu1
New Contributor III

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

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
Valued Contributor II

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

Hi,

please read this:

Set Analysis for Rolling Periods

HTH

André Gomes