Split of month-values into workingday-values

[reinholdgraf]

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

[sunny_talwar]

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

[reinholdgraf]

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)

[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?

[reinholdgraf]

so far not stored.

I am using QlikSense.

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

[sunny_talwar]

Yes, the above script should work in Qlik Sense also

[reinholdgraf]

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 ?

[sunny_talwar · Accepted solution]

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

];

[reinholdgraf]

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]);

[sunny_talwar]

Can you post a screenshot of the error message?