topic Rolling Forecast based on a month in QlikView

Rolling Forecast based on a month

Tue, 05 Jul 2011 13:23:15 GMT

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

Rolling Forecast based on a month

Wed, 06 Jul 2011 11:50:02 GMT

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

Anil_Babu_Samineni — Mon, 04 Jul 2016 16:55:26 GMT

Sravs,

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