Business/working days MTD vs. Prior Months

user06193

Hello All,

 

I am trying to figure out how to a MTD comparison between current month and prior months.

 

I want to compare my February MTD results the the prior months results assuming they were at the same MTD period as my current month. Hence if I am only at the 10 business day of the current month then I want to see where I was at on the 10th business day of prior months.

 

Im not sure if it should done this in the ETL or beast mode, any help would be great.

BlueRooster

The way we went about solving that here was to first create a table of "Business days of months" for large timeframe (ie, 2010-2200), as long as you have a way to reference what business day each day actually was/will be.

 

I then created an ETL using multiple left joins to my data using that date range to create "working day of month" for each date i care about.

SELECT a.*, b.`WorkingDayOfMonth` AS 'LeadWDOM', c.`WorkingDayOfMonth` AS 'CloseWDOM', (SELECT d.`WorkingDayOfMonth` FROM daterange as D WHERE d.`Date` = CURRENT_DATE()) AS 'CurrentWDOM'

FROM dataset as a

LEFT JOIN daterange as b ON a.`Lead Date` = b.`Date`

LEFT JOIN daterange as c ON a.`Close Date` = c.`Date`

 

 

From that transform you can then create beastmodes where my CASE WHEN statements check to make sure the dates I'm checking in previous months are <= my 'CurrentWDOM' column.

 

I know that was a very high level overview, if you need me to go into more detail just let me know what you need more help on.

 

Sincerely,
ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.