Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

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
Partner
Partner

Re: Help with line chart showing actuals and forecast

does anyone have any ideas?

Partner
Partner

Re: Help with line chart showing actuals and forecast

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)

Highlighted
Partner
Partner

Re: Help with line chart showing actuals and forecast

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
New Contributor III

Re: Help with line chart showing actuals and forecast

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!