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

**URL:** <https://pug.phocassoftware.com/t/summary-widget-show-last-day-with-sales-most-often-non-weekends-or-non-holidays/1693>\
**Category:** Ask the Community\
**Tags:** howto\
**Created:** [August 17, 2020, 1:10pm UTC](https://pug.phocassoftware.com/t/summary-widget-show-last-day-with-sales-most-often-non-weekends-or-non-holidays/1693 "2020-08-17T13:10:56Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![hakio](https://avatars.discourse-cdn.com/v4/letter/h/f19dbf/32.png) [@hakio](https://pug.phocassoftware.com/u/hakio)\
**Post date:** [August 17, 2020, 1:10pm UTC](https://pug.phocassoftware.com/t/summary-widget-show-last-day-with-sales-most-often-non-weekends-or-non-holidays/1693/1 "2020-08-17T13:10:57Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![Brendan](https://sea1.discourse-cdn.com/flex015/user_avatar/pug.phocassoftware.com/brendan/32/257_2.png) [@Brendan](https://pug.phocassoftware.com/u/Brendan)\
**Post date:** [August 19, 2020, 11:40am UTC](https://pug.phocassoftware.com/t/summary-widget-show-last-day-with-sales-most-often-non-weekends-or-non-holidays/1693/2 "2020-08-19T11:40:44Z")

</div>

Ask your rep/support contact to add Mon-Fri date range to your Database.  
 ![Capture](https://us1.discourse-cdn.com/flex015/uploads/phocassoftware/original/1X/0e5b21bcd342e672acc673ce1f4eb5d1a5e34341.jpeg)

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

---

<div class="post-metadata">

**Author:** ![hakio](https://avatars.discourse-cdn.com/v4/letter/h/f19dbf/32.png) [@hakio](https://pug.phocassoftware.com/u/hakio)\
**Post date:** [August 20, 2020, 7:11am UTC](https://pug.phocassoftware.com/t/summary-widget-show-last-day-with-sales-most-often-non-weekends-or-non-holidays/1693/3 "2020-08-20T07:11:36Z")

</div>

Thanks Brendan - I did exactly this already 🙂  
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.