One of the KPIs that companies often concentrate on is the average sales in a month. The average sales are calculated by dividing the total sales by the number of working days in a month.
Every month has a different number of working days. While calculating the number of working days for the current month, we only need to consider the days passed in the month and not the total days of the month in order to calculate the actual working days.
In order to arrive at the exact number of working days in a month, we need to exclude all the Fridays and Saturdays as well as the public and bank holidays from our calculations. The Networkdays() function helps us to achieve this.
For this exercise, we first need to prepare a list of all public holidays either in Excel or inline in the Qlik Sense script.
- Copy and paste the following part of the script in the data load editor. This is a list of public holidays for 2014 and 2015:
HolidayTmp: LOAD DATE(Date#( Date,'DD/MM/YYYY')) as Date INLINE [ Date 01/01/2015 03/04/2015 06/04/2015 04/05/2015 25/05/2015 31/08/2015 25/12/2015 28/12/2015 01/01/2014 18/04/2014 21/04/2014 05/05/2014 26/05/2014 25/08/2014 25/12/2014 26/12/2014 ];
- Next, we will store the list of public holidays in a variable inside the script:
ConcatTmp: LOAD concat(chr(39) & Date & chr(39),',') AS HolidayDates RESIDENT HolidayTmp; LET vPublicHolidays = FieldValue('HolidayDates',1); LET vCurMonth=month(today()); - Copy and paste the following fact table. Insert the last of the
PostingDatesin your table as today's date and put a sales figure against it. This is to demonstrate the use oftoday()in the theWorkingDayscalculation:SalesTmp: LOAD DATE(Date#( PostingDate,'DD/MM/YYYY')) as PostingDate, Sales INLINE [ PostingDate, Sales 05/08/2014, 5000 04/09/2014,522 24/10/2014,400 15/11/2014,5000 24/12/2014, 822 29/12/2014, 633 02/01/2015, 1000 02/02/2015, 2000 25/03/2015,2200 25/04/2015,266 09/05/2015, 3000 18/05/2015, 4000 15/06/2015,5000 22/07/2015,456 08/09/2015,4200 26/10/2015,1875 ];
- Next, calculate the number of working days:
Sales: LOAD *, Month(PostingDate) as Month, MonthName(PostingDate) AS MonthYear, IF(Year(PostingDate)=Year(TODAY()) AND Month(PostingDate)=MONTH(TODAY()), NETWORKDAYS(MONTHSTART(today()),(Today()), $(vPublicHolidays)), NETWORKDAYS(MONTHSTART(PostingDate), MonthEnd(PostingDate), $(vPublicHolidays))) AS WorkingDays RESIDENT SalesTmp; DROP table SalesTmp; DROP table HolidayTmp;
- Load the script.
- On the Qlik Sense sheet, create a table object and name it
Average Monthly Sales. - Add MonthYear and WorkingDays as dimensions.
- Add the following measure and label it as
Avg Sales:Sum(Sales)/WorkingDays
- Set the number formatting for Avg Sales to Money.
- Under Sorting, make sure that the
MonthYearfield is promoted to the top. - Go to Appearance | Presentation and switch off Totals.
- The final table object should look like this:
The Concat function stores the aggregated string concatenation of all the holiday dates. These holiday dates are stored in a variable vPublicHolidays, which is further used in the Networkdays() function.
The Networkdays() function has three parameters. The first two parameters define the range of dates to consider. If the PostingDate date lies in the current month, the range of dates is defined by the first day of the month and today. From this range, we exclude the non-working days Saturdays, Sundays, and public holidays.
If the posting date is in a month prior to the current month, the first and the last day of said month determine the range of the days for calculating the working days.