Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Rolling Forecast based on a month

Team,

Below is the requirement I am working on,

Rolling Average By 3 months, 6 months and 12 months

Need to comapre the current year rolling values with Previous Year rolling values based on month chosen.

If I have selected April 2011

Months             Previous yr                 Current year

3months        Jan-Mar Sales                    Jan-Mar Sales

                         of 2010                         of 2011

6months          Jan-Mar Sales(2010)               Jan-Mar Sales(2011)

                    + Oct - Dec(2009)                       + Oct - Dec(2010) 

The expression I am trying to use for Prev Yr is    

Sum({<Month = {"<=$(=max(Month))"},

     Year = {$(=(max(Year)-1))}  >}Quantity)

and for current year is

Sum({<Month = {"<=$(=max(Month))"},

     Year = {$(=(max(Year)))}  >}Quantity)

How is it possible to specify the range of the months?

I tried for 3 months for prev yr as below . But it brought 0 values.

Sum({<Month = {"<=$(=max(Month, -3))"},

     Year = {$(=(max(Year)-1))}  >}Quantity)

Please advise.

Regards,

Sudha

2 Replies
Not applicable

Rolling Forecast based on a month

Hi,


Why don't you set the months as selections (you need them as field in the model via a calendar for example) and then declare 2 variables CurrentYear and PreviousYear which you can use in set analyses:

CurrentYear =  Year(Today())

PreviousYear =  Year(Today()) - 1

Then in the expression:

Expr1_CurrentY = Sum({$<(Year = {$(CurrentYear)}>} Quantity)

Expr1_PrevY = Sum({$<(Year = {$(PreviousYear)}>} Quantity)

Then what ever months you select it will compare this year with previous year

Regards,

Jonathan

Re: Rolling Forecast based on a month

Sravs,

I have same BRD, If possible - Can you please share me that qvw or code.

Life is so rich, and we need to respect to the life !!!
Community Browser