Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Reply
johanlindell
Partner - Creator II
Partner - Creator II

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.