- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- split
- workingdays
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Would you be able to share few rows of data and explain what the desired output is?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
so far not stored.
I am using QlikSense.
Can I use the attached .qvw in QlikSense as well ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, the above script should work in Qlik Sense also
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you post a screenshot of the error message?
- « Previous Replies
-
- 1
- 2
- Next Replies »