Embedding Actual and Forecast data on same chart

[#1]

Hello All,

I am working on a project as an intern and learning Qlik Sense.

I have a dimension called Scenario which has Actual, Plan and Forecast. I also have a dimension called Months which has Jan till Dec. All the measures are stored in amount.

I am trying to create a chart which shows 13 months data having both Actual and Forecast data.

For example, the current month is Sep, then it will show Actual data from Dec 2015 till Sep 2016 and Forecast data from Oct 2016 till Dec 2016. Also it should be dynamic. suppose next month when I choose Oct as my month then it should show Actual data till Oct 2016 and Forecast of Nov till Dec.

Any guidance or example would be a great help to me.

Thanks in advance.

[Anonymous]

There should be two expressions in the chart:

1. Sum(Actual) - I assume here that there is no any Actual in the future.

2. Sum(if(Date>=ReloadTime(), Forecast))

As for the dimension (Months), you can limit it from 9 months back to 3 months forward either in the script, or in the chart itself (if you need more months for another purpose) using calculated dimension.

[#3]

Hello Michael,

Thank you so much for giving a great head start to my query.

I have dimension(Month) which just has Jan, Feb, Mar ...till Dec. I have a separate dimension called Year. And both are referenced in the fact table.

For limiting the previous(for actual) and next(for forecast) in script, will I have to create a 'As-of' table? And will I have to create a separate master calendar to create 'As-of' table? And also, How can I do it directly in my chart if I don't want to make changes to my script?

Sincerely,

Aum

[Anonymous]

If you want to show the time range based always on the current time, there is no need for "As Of".  You can create an additional field, specifically to use as a dimension in this chart, that has format combining month and year (e.g. MMM-YY), and that is limited from 9 months back to three months forward.

It is typically better to do this in the script that to use calculated dimensions - simpler front end, and better performance.

[#5]

Michael, your previous post has really helped me. I am able to show Jan-Sept  'Actual' data  and Oct-Dec 'Forecast' data.

But I have to show 13 month data, Dec 2015 to Dec 2016. The user wants the flexibility to choose the month and see actual data for previous months and Forecast data for next months.

For example, if user chooses Mar 2016, he wants to see actual data of Dec 2015 till March 2016 and Forecast from Apr 2016 till Dec. ( Date>=Reloadtime() wont provide this flexibility)

Please help me this.

Thanks in advance.

[Anonymous · Accepted solution]

You want the ability to choose the "current" month?  It is possible too.  Say, you have the Month (the one that contains these 13 values) as a field in your calendar.  So, your chart expressions will be

sum({<Month=, Date={"$(='<=' & date(max(Date)))"}>} Actual)          // on and before the end of the selected month

sum({<Month=, Date={"$(='>=' & date(min(Date)))"}>} Forecast)     // on and after the start of the selected month

Notice that I include the selected month in both expressions, so for this month you'll see both Actual and Forecast values.

[#7]

Michael,

Thank you so much for the help. Your solution has solved my requirements(100%). I used a combo chart with two separate expressions for Actual and Forecast.

I appreciate the help you extended to me for learning the Qlik concepts. I am marking this as correct answer so that others can refer and learn. Thanks again Michael.

[qlikviewforum]

I am also working something similar to this. In my case I think I may need to transform the data for the forecast dates or actual dates for the current and preceding years. 

Can you please help me out for this.

This is very very urgent!!!

https://community.qlik.com/t5/New-to-QlikView/Urgent-Help-required-to-transform-the-data-set-for-the...