Business/working days MTD vs. Prior Months

user06193
user06193 Member
in Archive

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.

Comments

  • Valiant
    Valiant Coach

    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.

Categories