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.
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 ];
ConcatTmp: LOAD concat(chr(39) & Date & chr(39),',') AS HolidayDates RESIDENT HolidayTmp; LET vPublicHolidays = FieldValue('HolidayDates',1); LET vCurMonth=month(today());
PostingDates
in your table as today's date and put a sales figure against it. This is to demonstrate the use of today()
in the the WorkingDays
calculation: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 ];
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;
Average Monthly Sales
.Avg Sales
:Sum(Sales)/WorkingDays
MonthYear
field is promoted to the top.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.