1 Reply Latest reply: Oct 27, 2016 4:35 AM by Johan Lindell

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

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.

DateYearMonthWeekdayWorkday
10/1/2016201610Saturday0
10/2/2016201610Sunday0
10/3/2016201610Monday1
10/4/2016201610Tuesday1
............

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

TableDateYearMonthSales Amount
Sales10/1/201620161010
Sales10/3/201620161020
Sales10/4/201620161010
Sales10/5/201620161010
Sales10/9/201620161040
...

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:

TableDateYearMonthRunRate Amount
RunRate10/1/201620161010
RunRate10/2/20162016100
RunRate10/3/201620161020
RunRate10/4/201620161010
RunRate10/5/201620161010
RunRate10/6/201620161010
RunRate10/7/201620161010
RunRate10/8/20162016100
RunRate10/9/20162016100
RunRate10/10/201620161010
............

Best regards,

Ibrahim

johanlindell maybe, you know what i mean. Thx again

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

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.