How to return weekdays count between the selected date range

Published:

This article provides a detailed guide on calculating the number of weekdays within a specified date range and also explains how to count days based on day names using expressions and dashboard parameters.

Calculating Weekdays Count

Usecase:

Organizations can analyze sales performance by counting weekdays in a date range, excluding weekends. This helps understand customer behavior and optimize sales strategies. The expression calculates the number of weekdays (Monday to Friday) within the selected range.

Steps to calculate weekdays count

Edit the existing data source and create a dashboard parameter with Mode as Literal with type as Date and then select Range in the selection type and configure the dashboard parameter with specific range.
Create expressions for the start date and end date using the dashboard parameter. Use the following syntax:@{{:ParameterName.START}} or @{{:ParameterName.END}}

Expression Name	SQL	PostgreSQL	Clickhouse
START DATE	CONVERT(DATE,@{{:Parameter1.START}})	CAST(@{{:Parameter1.START}} AS DATE)	toDate(@{{:Parameter1.START}})
END DATE	CONVERT(DATE,@{{:Parameter1.END}})	CAST(@{{:Parameter1.END}} AS DATE)	toDate(@{{:Parameter1.END}})

Use the below expression for get count of weekdays based on the selected date range.

SQL	PostgreSQL	Clickhouse
(DATEDIFF(day,[START DATE],[END DATE])+1) - (DATEDIFF(WEEK, [START DATE], [END DATE]) * 2) - (IF(DATEPART(dw,[START DATE])=1,1,0))-(IF(DATEPART(dw,[END DATE])=7,1,0))	( ([END DATE]-[START DATE]) + 1)- (FLOOR((([END DATE] - [START DATE]) + 1) / 7) * 2) - (IF(EXTRACT(DOW FROM [START DATE])=0,1,0))-(IF(EXTRACT(DOW FROM [END DATE])=6,1,0))	(date_diff(‘day’,[START DATE],[END DATE])+1)- (arrayReduce(‘sum’, arrayMap( x -> if(toDayOfWeek([START DATE] + x) IN (6, 7), 1, 0)),range(toUInt32(date_diff(‘day’,[START DATE],[END DATE])+1)))))

Now, the weekdays count will be displayed like below on binding the respective expression columns based on the selected date range.

Calculating the Count of Each Day in a Week

Usecase:

Organizations often find it beneficial to monitor the quantity of goods sold on a daily basis. This practice can provide valuable insights into overall sales performance, allowing for a clearer understanding of trends and patterns over time The below expression calculates each day count based on the selected date range.

Steps to calculate each day count

To calculate the count of days based on each day, follow these steps:

Use the Start date and End date from step 1 of the above calculation.
Add the expression for the Week Number of Start Date (Starting Date) by using the below expression.

SQL	PostgreSQL	Clickhouse
DATEPART(WEEKDAY,[START DATE])	EXTRACT(DOW FROM [START DATE])	toDayOfWeek([START DATE])

Use the below expression to get the count of days.

Days	SQL	PostgreSQL	Clickhouse
Monday	FLOOR((DATEDIFF(day,[START DATE],[END DATE]) - (IF([WeekNumber of Start date]>2,7-([WeekNumber of Start date]-2),IF([WeekNumber of Start date]<2,2-[WeekNumber of Start date],0))))/7)+1	FLOOR(([END DATE] - [START DATE] - IF([Week Number of Start Date] > 1 , 7 - ([Week Number of Start Date] - 1),IF([Week Number of Start Date] < 1 , 1 - [Week Number of Start Date] ,0)) )/ 7 ) +1	toUInt32(((DATEDIFF(day,[START DATE],[END DATE]) - IF([Week Number Of Start Date]> 1, 7 - ([Week Number Of Start Date]-1), IF([Week Number Of Start Date] <1, 1 - [Week Number Of Start Date], 0))) / 7)+1)
Tuesday	FLOOR((DATEDIFF(day,[START DATE],[END DATE]) - IF([WeekNumber of Start date]>3,7-([WeekNumber of Start date]-3),IF([WeekNumber of Start date]<3,3-[WeekNumber of Start date],0)))/7)+1	FLOOR(([END DATE] - [START DATE] - IF([Week Number of Start Date] > 2 , 7 - ([Week Number of Start Date]-2),IF([Week Number of Start Date] < 2 , 2 - [Week Number of Start Date],0)) )/ 7) + 1	toUInt32(((DATEDIFF(day,[START DATE],[END DATE]) - IF([Week Number Of Start Date]> 2, 7 - ([Week Number Of Start Date]-2), IF([Week Number Of Start Date] <2, 2 - [Week Number Of Start Date], 0))) / 7)+1)
Wednesday	FLOOR((DATEDIFF(day,[START DATE],[END DATE])-IF([WeekNumber of Start date]>4,7-([WeekNumber of Start date]-4),IF([WeekNumber of Start date]<4,4-[WeekNumber of Start date],0)))/7)+1	FLOOR(([END DATE] - [START DATE] - IF([Week Number of Start Date] > 3 , 7 -( [Week Number of Start Date]-3),IF([Week Number of Start Date] < 3 , 3 - [Week Number of Start Date],0)) )/ 7) + 1	toUInt32(((DATEDIFF(day,[START DATE],[END DATE]) - IF([Week Number Of Start Date]> 3, 7 - ([Week Number Of Start Date]-3), IF([Week Number Of Start Date] <3, 3 - [Week Number Of Start Date], 0))) / 7)+1)
Thursday	FLOOR((DATEDIFF(day,[START DATE],[END DATE])-IF([WeekNumber of Start date]>5,7-([WeekNumber of Start date]-5),IF([WeekNumber of Start date]<5,5-[WeekNumber of Start date],0)))/7)+1	FLOOR(([END DATE] - [START DATE] - IF([Week Number of Start Date] > 4 , 7 -( [Week Number of Start Date]-4),IF([Week Number of Start Date] < 4 , 4 - [Week Number of Start Date],0)) )/ 7) + 1	toUInt32(((DATEDIFF(day,[START DATE],[END DATE]) - IF([Week Number Of Start Date]> 4, 7 - ([Week Number Of Start Date]-4), IF([Week Number Of Start Date] <4,4 - [Week Number Of Start Date], 0))) / 7)+1)
Friday	FLOOR((DATEDIFF(day,[START DATE],[END DATE])-IF([WeekNumber of Start date]>6,7-([WeekNumber of Start date]-6),IF([WeekNumber of Start date]<6,6-[WeekNumber of Start date],0)))/7)+1	FLOOR(([END DATE] - [START DATE] - IF([Week Number of Start Date] > 5 , 7 - ([Week Number of Start Date]-5),IF([Week Number of Start Date] < 5 , 5 - [Week Number of Start Date],0)) )/ 7) + 1	toUInt32(((DATEDIFF(day,[START DATE],[END DATE])- IF([Week Number Of Start Date]> 5, 7 - ([Week Number Of Start Date]-5), IF([Week Number Of Start Date] <5, 5 - [Week Number Of Start Date], 0))) / 7)+1)
Saturday	FLOOR((DATEDIFF(day,[START DATE],[END DATE])-IF([WeekNumber of Start date]>7,7-([WeekNumber of Start date]-7),IF([WeekNumber of Start date]<7,7-[WeekNumber of Start date],0)))/7)+1	FLOOR(([END DATE] - [START DATE] - IF([Week Number of Start Date] > 6 , 7 -([Week Number of Start Date]-6),IF([Week Number of Start Date] < 6 , 6 - [Week Number of Start Date],0)) )/ 7) + 1	toUInt32(((DATEDIFF(day,[START DATE],[END DATE]) - IF([Week Number Of Start Date]> 6, 7 - ([Week Number Of Start Date]-6), IF([Week Number Of Start Date] <6, 6 - [Week Number Of Start Date], 0))) / 7)+1)
Sunday	FLOOR((DATEDIFF(day,[START DATE],[END DATE])-IF([WeekNumber of Start date]>1,7-([WeekNumber of Start date]-1),IF([WeekNumber of Start date]<1,1-[WeekNumber of Start date],0)))/7)+1	FLOOR(([END DATE] - [START DATE] - IF([Week Number of Start Date] > 0 , 7 - [Week Number of Start Date],IF([Week Number of Start Date] < 0 , 0 - [Week Number of Start Date],0)) )/ 7) + 1	toUInt32(((DATEDIFF(day,[START DATE],[END DATE]) - IF([Week Number Of Start Date]> 7, 7 - ([Week Number Of Start Date]-7), IF([Week Number Of Start Date] <7, 7 - [Week Number Of Start Date], 0))) / 7)+1)