Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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
does anyone have any ideas?
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)
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?
If you have Excel 2016, you would be able to do it with these steps
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!