Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.