Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
reinholdgraf
Contributor

Split of month-values into workingday-values

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

Tags (2)
1 Solution

Accepted Solutions

Re: Split of month-values into workingday-values

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

];

15 Replies

Re: Split of month-values into workingday-values

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

Highlighted
reinholdgraf
Contributor

Re: Split of month-values into workingday-values

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

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
Contributor

Re: Split of month-values into workingday-values

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

Yes, the above script should work in Qlik Sense also

reinholdgraf
Contributor

Re: Split of month-values into workingday-values

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

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
Contributor

Re: Split of month-values into workingday-values

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

Re: Split of month-values into workingday-values

Can you post a screenshot of the error message?