# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
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
MVP

## 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:

FROM ....;

Table:

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;

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
MVP

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

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)

MVP

## Re: Split of month-values into workingday-values

May be like this

Table:

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;

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?

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 ?

MVP

## Re: Split of month-values into workingday-values

Yes, the above script should work in Qlik Sense also

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 ?

MVP

## 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:

FROM ....;

Table:

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;

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

];

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]:

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;

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",

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

MVP

## Re: Split of month-values into workingday-values

Can you post a screenshot of the error message?