Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
leivers1
Contributor II
Contributor II

Create table in load and fill columns with values from existing tables

Hi,

I am struggling how to do this. I have a Forecast Table with  

Forecast Cycle (2020-M03)

Year Month

(A forecast Cycle will forecast for 24months in future)

Customer GroupProductForecast 

 

I have a sales table with 

Year MonthCustomer GroupProduct

Sales

 

I want to create a final table comparing  the Sales minus Forecast For each Year month Product Customer group combination.

Month YearProduct idCustomer GroupSalesForecast-1Forecast-2 etc

 

In Final Table i want 12 columns (Forecast -x) and in those i want to populate;

For the current row where Month Year = 2020-01 I want the column Forecast-1 so be populate with the Sum of the value forecasted in the cycle of Month year -1 for that MonthYear and its Product/Group combination. and so on for the Forecast Cycle of Month year -2.

i.e if looking at 2020-03 year month; in Forecast -1 i expect to see the value of that was forecast in 2020-02 version but for the month 2020-03 

 

I am really struggling here; i have tried to pivot the Forecast data so that it is also on month year level.

I am thinking a for loop is needed here but i am very weak in this area.

 

Thanks,

Lara

1 Solution

Accepted Solutions
jwjackso
Specialist II
Specialist II

2 Replies
jwjackso
Specialist II
Specialist II

leivers1
Contributor II
Contributor II
Author

Thanks so much for your quick reply. i think  now have my data in the correct format (below) where i have my sales and the forecast values together by year month.

leivers1_0-1606416684417.png

But how can i create and populate new columns Forecast -1,-2 etc. using this new table.

I want; that if the row's Year month value is 2020-03; then i want to populate the column "Forecast-1" with the value in the column "2020-02" for that row and repeat this for the row; up until Forecast -12.

THanks,

Laura