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

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

    Ibrahim Düzenli

      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