Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help with line chart showing actuals and forecast

Hi all,

i am having some issues when trying to plot forecast data in a line chart.

the forecast data is provided at the start of each period. it is in the following format..

Capture.JPG

so basically if the current period is period 10, then i would take actual sales from period 1-9 then add the forecast sales for period 10-13.

on the spreadsheet provided this period, the column 'Curr Pd' would be Period 10, 'P+1' would be Period 11 'P+2' = Period 12 etc

obviously, the field 'Curr pd' would not be linked to Period 10, so i think this is where i am having problems as i don't know how to include the correct forecasts based on the current period.

the data is by Item and customer.

I have attached some sample files if anyone could help at all.

thanks

4 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

does anyone have any ideas?

stascher
Partner - Creator II
Partner - Creator II

If I understand what you're trying to do, I would load that data into a table with  the format:

Item, Customer, Period, Value

0001,     Cust A,         1,     46

0001,     Cust A,          2,    20

etc..

Then, given a current period assigned to say a variable vCurrentPeriod

create a chart with Item or Customer domains and two measures; the first to aggr data where Period <= vCurrentPeriod and the second to aggr data where Period > vCurrentPeriod. For example: Sum( {<Period={'<=$(vCurrentPeriod)'}>} Value)

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, thanks for your reply.

I cant get my head around how to create a table in that format from the source data.

are you able to point me in the right direction?

thomasreynaert
Contributor III
Contributor III

If you have Excel 2016, you would be able to do it with these steps

  • Insert > Table > Select your data
  • Data > From Table
  • Select the Period Columns > Unpivot
  • Close & Load

If you have an earlier version you can download the Power Query add-in to do this.

However for big data sets >>> more rows than fit in an simple excel you will need to create this format using a script or have it in a QVD file.

Good luck!