Summary/Widget, show last day with sales (most often non-weekends or non-holidays)

Tips and tricks
1

What is the best way to setup a “Last days sales”-widget?

That is, on Monday it should show me Fridays rev., if no sales was recorded over the weeekend.
It should also consider holidays / non-working days.
Either looking at a calendar or by just simply taking the last days figure that are different to 0. The latter I cannot think of how to archieve, even though it sounded simply when I thought about it…

I also thought about using “Working Days” concept, and looking up the it’s defined non-working days via SQL to auto-generate a new Custom Period where non-working days (mainly weekends+holidays) are moved to the nearest working day, ie. so a Friday day-period would actually have startDate on Friday and endDate on Sunday. Things would get somewhat complex though…! But it would allow using the Working Days calendar feature to outline non-working days…

2

Ask your rep/support contact to add Mon-Fri date range to your Database.
Capture

This works for us - means that when you look at sales figures for ‘last day’ rather than ‘yesterday’… so on a Monday it will show Friday’s sales, rather than Sunday.

all the best

2 Likes
3

Thanks Brendan - I did exactly this already :slight_smile:
Good to hear this was not way off!

But the thing is, it doesn’t take into account holidays. The weekends are rather easy to handle automatically, so any revenue in weekends (negative, 0 or positive figures) are moved to Fridays. And this works like a charm… But the business wanna have it so either all weekends+holidays are included, or just having shown the last day with a revenue different from 0.

FYI I used this Custom Actions script - which generates daily periods (minus weekends) for the next 36 months (credit to Phocas for creating most of it!):

Declare @startdate date
Declare @enddate date

Set @startdate = ‘01/01/2020’
Set @enddate = dateadd(mm, 36, getdate())

DROP TABLE DATE_TradingDate;
CREATE TABLE DATE_TradingDate (
[Name] varchar(255),
[StartDate] Datetime,
[EndDate] Datetime,
[Year] varchar(255),
[FirstDayofMonth] varchar(255),
[LastDayofMonth] varchar(255),
[StartDayName] varchar(255),
[EndDayName] varchar(255),
[NewStartDate] Datetime,
[NewEndDate] Datetime

);
–select * from [DATE_TradingDate];

/* DEPRECATED
–IF OBJECT_ID(‘DATE_TradingDate’) IS NULL SELECT ‘’ [Name], ‘’ [StartDate], ‘’ [EndDate], ‘’ [Year], ‘’ [FirstDayofMonth], ‘’ [LastDayofMonth], ‘’ [StartDayName], ‘’ [EndDayName], ‘’ [NewStartDate], ‘’ [NewEndDate] INTO [DATE_TradingDate]
–IF OBJECT_ID(‘DATE_TradingDate’) IS NOT NULL Truncate table [DATE_TradingDate]
*/

WHILE (@startdate <= @enddate)
BEGIN
Insert into [DATE_TradingDate] (
[Name]
,[StartDate]
,[EndDate]
,[Year] )
SELECT Convert(varchar(10),CONVERT(date, @startdate,103),103) as [Name]
,@startdate as [StartDate]
,@startdate as [EndDate]
,year(@startdate) as [Year]
–,[FirstDayofMonth]
–,[LastDayofMonth]
–,[StartDayName]
–,[EndDayName]
–,[NewStartDate]
–,[NewEndDate]

SET @startdate = dateadd(dd, 1, @startdate)

END

update [DATE_TradingDate] set [FirstDayofMonth] = IIF([StartDate] = DATEADD(month, DATEDIFF(month, 0, [StartDate]), 0), 1, 0)
update [DATE_TradingDate] set [LastDayofMonth] = IIF([EndDate] = EOMONTH([EndDate]), 1, 0)
update [DATE_TradingDate] set [StartDayName] = DATENAME(dw, [StartDate])
update [DATE_TradingDate] set [EndDayName] = DATENAME(dw, [EndDate])

update [DATE_TradingDate] set [NewStartDate] = case when StartDayName in (‘Monday’) and day(startdate) = 3 then dateadd(dd, -2, [startdate])
when StartDayName in (‘Monday’) and day(startdate) = 2 then dateadd(dd, -2, [startdate])
when StartDayName in (‘Monday’) and day(startdate) = 1 then dateadd(dd, -1, [startdate]) end

update [DATE_TradingDate] set [NewEndDate] = case when EndDayName = ‘Friday’ and (startdate) = dateadd(dd,-2, eomonth(startdate)) then dateadd(dd, 1, [EndDate])
when EndDayName = ‘Friday’ and (startdate) = dateadd(dd,-1, eomonth(startdate)) then dateadd(dd, 0, [EndDate])
when EndDayName = ‘Friday’ and (startdate) = dateadd(dd,-0, eomonth(startdate)) then dateadd(dd, 0, [EndDate])
when EndDayName = ‘Friday’ then dateadd(dd, 2, [EndDate]) end

update [DATE_TradingDate] set [NewStartDate] = isnull([NewStartDate], [StartDate])
update [DATE_TradingDate] set [NewEndDate] = isnull([NewEndDate],[EndDate])

Delete from [DATE_TradingDate] where [StartDayName] in (‘Saturday’,‘Sunday’)

select * from [DATE_TradingDate];

And then I created a Sync View Item (requires phocas-user access I think…) that just took the content from the [DATE_TradingDate]-table.
This Item was then used as input to a Period Type.