15 Replies Latest reply: Feb 5, 2018 9:53 AM by Reinhold Graf Branched to a new discussion. RSS

    Split of month-values into workingday-values

    Reinhold Graf

      Hi.

       

      I need to split our plan-sales (bases on months) into workingdays.

      Theoretically, I have to split each Month-Plansales into the individual working-days of that month.

      Likely thru a Loop / AutoGenerate.

       

      For the formula, I thought about:

      Plan-WorkingdaySales = Plan-MonthSales / (networkdays (monthstart(Date),monthend(Date))

       

      Unfortunately, I have no clue how to set-up the Loop.

       

      Regards,

      Reinhold

        • Re: Split of month-values into workingday-values
          Sunny Talwar

          Would you be able to share few rows of data and explain what the desired output is?

            • Re: Split of month-values into workingday-values
              Reinhold Graf

              Sure.

               

              Input:

              SaleRep  Month  PlanSales (Month)

              04           Jan      50000

              04           Feb      60000

              04           Mar      70000

               

              Output:

              SaleRep  Month  Day        PlanSales (Day)

              04           Jan      1.1.18     0 (labour-day)

              04           Jan      2.1.18     2500  (20 working days)

              04           Jan      3.1.18     2500  (20 working days)

              .

              .

              .

              04          Mar      10.3.18    3333 (21 working days)

                • Re: Split of month-values into workingday-values
                  Sunny Talwar

                  May be like this

                   

                  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

                  ];

                   

                  You seem to have some other holidays in addition to Saturday and Sunday. Where is that stored?

                    • Re: Split of month-values into workingday-values
                      Reinhold Graf

                      so far not stored.

                       

                      I am using QlikSense.

                      Can I use the attached .qvw in QlikSense as well ?

                        • Re: Split of month-values into workingday-values
                          Sunny Talwar

                          Yes, the above script should work in Qlik Sense also

                            • Re: Split of month-values into workingday-values
                              Reinhold Graf

                              One little bug arose...

                               

                              Matching the weekdays for Sat/Sun verifies if workingday, or not.

                              Additionally, on labour-days, there should no PlanSales (Day) be calculated.

                               

                              In the networkdays-formula, I can include those labour-days.

                              But how can I now set the PlanSales (Day) for labour-days = 0 ?

                              Best practice ?

                                • Re: Split of month-values into workingday-values
                                  Sunny Talwar

                                  how are you loading those labour-days? Are they available in an Excel file? If they are, you can do something like this

                                   

                                  Labour_Days:

                                  LOAD Labour_Days

                                  FROM ....;

                                   

                                  Table:

                                  LOAD *,

                                  If(Match(WeekDay(Date(MonthStart + IterNo() - 1)), 'Sat', 'Sun') or Exists(Labour_Days, Date(MonthStart + IterNo() - 1)), 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

                                  ];

                                    • Re: Split of month-values into workingday-values
                                      Reinhold Graf

                                      OK.

                                      Getting closer now.

                                      Script produces "Unknown Error" on the Concatenate Load. (This needs to be done for 10 Divisions)

                                      Script looks like:

                                       

                                      [Feiertage]:

                                          LOAD    Datum    as    Feiertag

                                          FROM [lib://QlikDaten (ridigruppe_gaartz)/QS_Tabellen.xlsx]

                                          (ooxml, embedded labels, table is Feiertage);

                                       

                                       

                                      [tmp_UMSATZ_PLAN]:

                                          LOAD *,

                                       

                                              If(

                                                  Match(WeekDay(Date(MonthStart + IterNo() - 1)), 'Sa', 'So') or

                                                  Exists(Feiertage,Date(MonthStart + IterNo() - 1)),

                                                  0,

                                                  [xUE_W_P_AJ_LC]/WorkingDays

                                                  ) as [UE_W_P_AJ_LC],

                                              If(

                                                  Match(WeekDay(Date(MonthStart + IterNo() - 1)), 'Sa', 'So') or

                                                  Exists(Feiertage,Date(MonthStart + IterNo() - 1)),

                                                  0,

                                                  [xUE_W_PO_AJ_LC]/WorkingDays

                                                  ) as [UE_W_PO_AJ_LC],

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

                                       

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

                                       

                                       

                                          LOAD *,

                                       

                                              MonthStart(Date#(JahrMonat, 'YYYY-MM'))             as MonthStart,

                                              Date(Floor(MonthEnd(Date#(JahrMonat, 'YYYY-MM'))))     as MonthEnd,

                                              NetWorkDays(

                                                  MonthStart(Date#(JahrMonat, 'YYYY-MM')),

                                                  Floor(MonthEnd(Date#(JahrMonat, 'YYYY-MM'))),

                                                  '01.01.18','06.01.18','30.03.18','02.04.18','01.05.18','10.05.18','21.05.18','31.05.18','03.10.18','01.11.18','25.12.18','26.12.18'

                                                          )                                         as WorkingDays;

                                       

                                      // ----------------------------------------------------------------------//

                                      // PLAN-Umsätze laden (ab 2018)    Division 100

                                      // ---------------------------------------------------------------------//

                                          Concatenate

                                          Load     '100'                     as "Division.Nr",

                                                  Vertreter                as "Vertreter.Nr",

                                                  Kunde                    as "Kunde.Nr",

                                                  'ADM'                    as "Herkunft.Nr",

                                                  date(Datum,'YYYY-MM')    as "JahrMonat",

                                                  Planumsatz                as "xUE_W_P_AJ_LC",

                                                  Planumsatz                as "xUE_W_PO_AJ_LC"

                                      FROM [lib://QlikDaten (ridigruppe_gaartz)/RIDI DE Umsatzplanung 2018.xlsx]

                                      (ooxml, embedded labels, header is 1 lines, table is [Datenübergabe QS]);