Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
DaithiOK
Contributor II
Contributor II

Plotting data on a line chart with a 2 month delay

I am trying to plot a line chart but running into an issue with my expression.

 

My dimension is a variable called ReportMonth. The data I am trying to plot has a 2 month delay on it. For example the May data on the chart below is really from March. April is from Feb and so on. This is well known in the business and for this KPI everyone knows the data published each month is on a 2 month delay. 

The formula for this KPI in my table is as follows:

 

Sum(
{< 
VariousOtherFilters..., %ReportMonth1 = {$(vCurrentMonth)}, ForecastMonth = {$(vCurrentMonth-2)}}
>}
[Amount])

 


which essentially looks at the month of the report which is the current month and then takes the ForecastMonth which is 2 months ago. This works fine in a table as it only needs to look at one Report Month at a time. If i then change ReportMonth in my filter to April that would have vCurrentMonth = April and so ForecastMonth would be 2 months previous and so on. 

My problem is that in my line chart the ReportMonth is my dimension as below . In the line line chart my ForecastMonth filter obviously doesnt work as intended except for the current month as ReportMonth is seelcted in an external filter. If i select a previous month then that month on my line chart is correct. Below for example the May data is correct but the April data is not. The line should also extend back to Jan. 

 

DaithiOK_0-1590414209489.png

 

Labels (1)
2 Replies
chrismarlow
Specialist
Specialist

Hi,

As long as your dates behave I would probably try using above function similar to the below to try to get the offset working;

20200526_2.png

Cheers,

Chris.

DaithiOK
Contributor II
Contributor II
Author

Oh this is interesting. Thanks for your response. I'm not sure how it would work though. In your example you are summing a column of values called forecast. In my data set the forecast refers to a month value and Date is not the 'Index' of the table so if i did Above() it would likely just take the amount from 2 lines above within the same category and not 2 months previously. 

Thanks for your response though I always learn something on these forums that I never knew.