5 Replies Latest reply: Feb 15, 2018 8:49 AM by Sunny Talwar Branched from an earlier discussion. RSS

    Re: Split of month-values into workingday-values

    Sunny Talwar

      Script should be the same for QlikView or Qlik Sense

       

      Table:

      LOAD *,

      If(Match(WeekDay(Date(MonthStart + IterNo() - 1)), 'Sat', 'Sun'), 0, [PlanSales (Month)]/WorkingDays) as [PlanSales (Day)],

      Date(MonthStart + IterNo() - 1) as Date

      While Date(MonthStart + IterNo() - 1) <= MonthEnd;

      LOAD *,

      MonthStart(Date#(MonthYear, 'MMM-YYYY')) as MonthStart,

      Date(Floor(MonthEnd(Date#(MonthYear, 'MMM-YYYY')))) as MonthEnd,

      NetWorkDays(MonthStart(Date#(MonthYear, 'MMM-YYYY')), Floor(MonthEnd(Date#(MonthYear, 'MMM-YYYY')))) as WorkingDays;

      LOAD * INLINE [

          SaleRep, MonthYear, PlanSales (Month)

          04, Jan-2018, 50000

          04, Feb-2018, 60000

          04, Mar-2018, 70000

      ];