Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Stefan_Z
New Contributor

Compare Working days MTD with previous year (dependent on working days, not date)

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:

  1. 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.
  2. 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