Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
guillaume_gorli
Creator II
Creator II

6 month rolling forecast

Hi All,

Here is the issue i am facing :

i have a database showing for each month a sales forecast :

Capture.JPG

The idea is to show a 6 month forecast based on the period selected

For instance, if December 2016 is selected, the outpout should be the data for the 6 following period (from January to June 2017) as seen below :

Capture.JPG

If February 2017 is selected, then the output should be the data from March to August 2017 as seen below :

Capture.JPG

For those who want to help, let me attached the Excel test database as well as the .qvw file

Thanks in advance

Guillaume

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

sum({<Année_Mois={">$(=max(Année_Mois))<$(=addmonths(max(Année_Mois),6))"},[$(=concat({<$Table={'Calendrier'},$Field-={'Année_Mois'}>} $Field,'],['))]>} [Forecast Sales Zone 1])

The concat() part of the expression is perhaps overkill, but it lets you add or remove Calendrier fields without having to modify the expression.

However, you might consider using an AsOf table connecting each month to the 6 future months. Much simpler expression. But there can then be confusion about which field is being selected, and if the wrong one is selected, strange results can happen.

AsOf_Mois, Année_Mois
Feb 2017, Mar 2017
Feb 2017, Apr 2017
...
Feb 2017, Aug 2017

Dimension  = AsOf_Mois
Expression = sum([Forecast Sales Zone 1])

The As-Of Table

View solution in original post

2 Replies
johnw
Champion III
Champion III

sum({<Année_Mois={">$(=max(Année_Mois))<$(=addmonths(max(Année_Mois),6))"},[$(=concat({<$Table={'Calendrier'},$Field-={'Année_Mois'}>} $Field,'],['))]>} [Forecast Sales Zone 1])

The concat() part of the expression is perhaps overkill, but it lets you add or remove Calendrier fields without having to modify the expression.

However, you might consider using an AsOf table connecting each month to the 6 future months. Much simpler expression. But there can then be confusion about which field is being selected, and if the wrong one is selected, strange results can happen.

AsOf_Mois, Année_Mois
Feb 2017, Mar 2017
Feb 2017, Apr 2017
...
Feb 2017, Aug 2017

Dimension  = AsOf_Mois
Expression = sum([Forecast Sales Zone 1])

The As-Of Table

guillaume_gorli
Creator II
Creator II
Author

Thanks a lot John !