Hi, maybe you can help me with this problem. We would like to compare number of current month working days with the same number of working days in the previous year MTD.
Due to the weekends and holidays, the prior year date does not contain the same number of working days. For example (09.03.2019 = today):
MTD 01.03. - 09.03.2018 = 7 working days
MTD 01.03. - 09.03.2019 = 6 working days
What we want to do is comparing the 09.03.2019 with the 08.03.2018 (= 6 working days CY and PY). It can happen that we have minor sales at weekends which are shown on the previous working day.
When I create a table to compare the numbers, the table shows the 7 working days of the previous year and since we do not have data for the 7th working day (which would be tomorrow), it shows me a straight line.
I am looking for the following two expressions:
- WorkingDaysMTD showing for this year and prior year the number of working days limited to the max working days of the current year à to limit the table to 6 working days.
- PY.CurMTD_CYWorkingDays: A flag indicating the days to compare of the previous year only à to calculate the number of bookings of the previous year.
To calculate the working days we currently use the following expression in the master calendar:
Arbeitstag# - ArbeitstagMonthStart# + 1 as ArbeitstageMTD
rangesum(peek(Arbeitstag#),
if(match(num(weekday(TempDate)),5,6) // Saturday/Sunday no working day
or applymap('mapFeiertage',TempDate,0)=1, // Lookup bank holidays
0, // +0 wenn kein Arbeitstag
1) // +1 wenn Arbeitstag
) as Arbeitstag#,
Any ideas? Thanks
Stefan
