Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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!

3 Replies
Highlighted
Contributor III
Contributor III

Harry,

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

Highlighted
Contributor III
Contributor III

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.

Highlighted
Specialist II
Specialist II

Hi,

please read this:

Set Analysis for Rolling Periods

HTH

André Gomes