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

# 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

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

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

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;

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

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

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

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;

SaleRep, MonthYear, PlanSales (Month)

04, Jan-2018, 50000

04, Feb-2018, 60000

04, Mar-2018, 70000

];

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

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

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

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

Can you post a screenshot of the error message?

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

unknown error ---

we have 3 different Loads in one Section.

How is the order for the system to process these Loads ?

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

Not sure I understand your question, do you mind elaborating?

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

top-down or bottom-up ?

I need to understand, how the systems processes your script-example to find the error.

(your script-example with inline-data works fine).

I first create a tmp_file containing all divisions with all plan-sales (monthly).

With this file (resident), I want to use your script to create daily-plansales and further calculations (e.g. currency-conversions, etc.)

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

You are using preceding loads in your script, these are executed bottom to top.

What is the table source of your LOAD statement labelled with tmp_Umsatz_Plan?

Sunny was using an INLINE table, what table source do you intend to load? the Excel file?

Then remove the Concatenate LOAD prefix, it should be used only on the topmost LOAD statement.

If you need to use multiple files, create a concatenated table before, then use a resident table to use Sunny's script snippet.

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

OK.

That's what I did.

1. Create a concatenated table (tmp_UMSATZ_PLAN1) from various excel-files

2. Use this table in the snippet

I removed now the "Concatenate", but with the same error-message when loading the data

How shall I find an "unknown error" ???

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

Solved now !

problem was within the EXISTS-Formula.