Qlik Sense, calculate future prognose based on past data <=Today() in load editor

[#1]

Hello Qlik Community,

i have a big matter. Hopefully, you can help me. I would like to calculate a RunRate (prognose) for the current month. RunRate is a future prognose for the current month based on past data of current month. I would like to explain my matter with three tables.

First, I upload a calendar table similar to this calendar. The calendar contains all the date information.

Date	YearMonth	Weekday	Workday
10/1/2016	201610	Saturday	0
10/2/2016	201610	Sunday	0
10/3/2016	201610	Monday	1
10/4/2016	201610	Tuesday	1
...	...	...	...

Second, I upload a sales table similar to this table. The sales table doesn't contains all the date information.

Table	Date	YearMonth	Sales Amount
Sales	10/1/2016	201610	10
Sales	10/3/2016	201610	20
Sales	10/4/2016	201610	10
Sales	10/5/2016	201610	10
Sales	10/9/2016	201610	40
...

1. If a date is not in Sales Table, then for RunRate Amount = 0.

2. The Average of Sales Amount between First date of month (10/1/2016) and Today(10/5/2016) = 10.

3. I would like to have in the new Table in RunRate Amount for  IF(Workday = 1, then Average 10, IF(Workday = 0, Then =0.

4. Until End of Current Month.

Desired result:

Table	Date	YearMonth	RunRate Amount
RunRate	10/1/2016	201610	10
RunRate	10/2/2016	201610	0
RunRate	10/3/2016	201610	20
RunRate	10/4/2016	201610	10
RunRate	10/5/2016	201610	10
RunRate	10/6/2016	201610	10
RunRate	10/7/2016	201610	10
RunRate	10/8/2016	201610	0
RunRate	10/9/2016	201610	0
RunRate	10/10/2016	201610	10
...	...	...	...

Best regards,

Ibrahim

johanlindell‌ maybe, you know what i mean. Thx again

[johanlindell]

Well, the I have to reply, won't I.

For the current month calculate an average based upon the second table. Make a loop over the remaining dates and "Autogenerate" a record for each remaining date with the date and the average.