6 Replies Latest reply: Oct 3, 2016 11:39 PM by Aum Upadhyay RSS

    Embedding Actual and Forecast data on same chart

    Aum Upadhyay

      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.

        • Re: Embedding Actual and Forecast data on same chart
          Michael Solomovich

          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.

            • Re: Embedding Actual and Forecast data on same chart
              Aum Upadhyay

              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

                • Re: Embedding Actual and Forecast data on same chart
                  Michael Solomovich

                  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.

                    • Re: Embedding Actual and Forecast data on same chart
                      Aum Upadhyay

                      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.

                        • Re: Embedding Actual and Forecast data on same chart
                          Michael Solomovich

                          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.