Custom NetworkDays

[NormanStanleyBadger]

Hi, I hope everybody is well. 😎

I trying to the measure the number days between customer purchases, but I do not want to count days when the business is closed. Unfortunately, while there is a general pattern when the business is open, it is not unusual for it to open on the weekend once or twice a month and on national holidays. Therefore once I have established the split between open days and closed days. I then need a way of removing the closed days from any calculation between a customer's purchases.

Thanks.

NSB

[RafaelBarrios]

Hi @NormanStanleyBadger 

i think this may help

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeF...

networkdays ('19/12/2013', '07/01/2014')
Returns 14. This example does not take holidays into account.

networkdays ('19/12/2013', '07/01/2014', '25/12/2013', '26/12/2013')
Returns 12. This example takes the holiday 25/12/2013 to 26/12/2013 into account.

networkdays ('19/12/2013', '07/01/2014', '25/12/2013', '26/12/2013', '31/12/2013', '01/01/2014')
Returns 10. This example takes two holiday periods into account.

Hope this helps.

help users find answers! Don't forget to mark a solutions that worked for you & to smash the like button!  

[NormanStanleyBadger]

Hey @RafaelBarrios , thanks for taking the time to reply. 👍

Sorry, having re-read my original comment, I should have been more specific. Ultimately, open and closed days can be any day of the week, so as networkdays automatically excludes weekends it is a non-starter for me in its original form.

I need the difference between two days (including all days of the week) less closed days. Networkdays gives me the difference between two dates - less weekends - less closed days.

[rwunderlich · Accepted solution]

Here's a potential approach using script. You will need a table of all dates with a field indicating Open (1) or Closed(0).  I faked one in here. 

Schedule:
Load
*,
-(WeekDay(ScheduleDate) < 5) as Open
;
LOAD
Date(today(1) - RecNo()) as ScheduleDate
AutoGenerate 365
;

Purchases:
LOAD *, RecNo() as OrderId Inline [
Customer, PurchaseDate
A, 9/1/2023
A, 8/14/2023
A, 6/1/2023
B, 6/16/2023
B, 3/1/2023
B, 2/1/2023
]
;

Purchases2:
LOAD
*,
if (Customer = Previous(Customer), Peek('PurchaseDate')) as PreviousPurchase
Resident Purchases
Order By Customer, PurchaseDate
;
Drop Table Purchases;

Left Join(Schedule)
Match:
IntervalMatch (ScheduleDate)
Load  PreviousPurchase, PurchaseDate
Resident Purchases2
;

Left Join (Purchases2)
LOAD
PreviousPurchase, PurchaseDate,
Sum(Open) as OpenDaysBetween
Resident Schedule
Group By PreviousPurchase, PurchaseDate
;
Drop Table Schedule;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[NormanStanleyBadger]

Thank you, this is great 😃. I'd gone away to research the problem and while I am feeling pleased that through that I have managed to produce the first two parts of this script on my own (open vs closed, previous purchase). I'm also feeling a bit daft that I'd have saved myself all that effort, if I had returned sooner 😞. I have learnt a bit in the process, I suppose. I was completely stuck on how to do the actual OpenDaysBetween calculation, though, so thanks again. 👍 Top man.

[NormanStanleyBadger]

Hi, I am close on this but I believe that because I have more than one customer with the same purchase and last purchase dates the daysbetween calculation is returning a multiple of the number of records. How would I adapt the code so that I can get the right calculation for each iteration? I have started by adding Customer to the Interval Match table?  Do I need to create a key for customer_purchasedate?

Left Join(Schedule, Customer)
Match:
IntervalMatch (ScheduleDate)
Load  

PreviousPurchase, 

PurchaseDate,

Customer
Resident Purchases2
; 

[rwunderlich]

For starters try adding Customer to the IntervalMatch as well.

IntervalMatch (ScheduleDate, Customer)

-Rob