Count of Business Days with activity mtd year over year

SEC
SEC Contributor
in Charting

I currently compare business days with unit activity yoy using a yoy bar card set to MTD and displayed by day.  

 

I'm looking for a better solution that will just show the count of business days to date in the month with activity and what it was through the same date a year ago.  Does anyone have something that gives that comparison easily?  I need something simple that a c level person can look at to know right away if we have comparible days or not.

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    So the first step is to pair the 'Working Day of Month' to each date in your range. If you don't already have that, here's a table that I use (accounts for major holidays) see attached. 

     

    Join that to your date that you're using. I would also include a constant for CurrentDay and Join to that to get the current working day of month. Once you have that I have 2 separate measures for each of my years in a Year over Year chart (using the grouped bar + line chart type). The current year is just

    CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE()) THEN `Amount` END

    For the previous year I do something like this:

    CASE WHEN YEAR(`Date`) = (YEAR(CURRENT_DATE())-1) 
    THEN CASE WHEN MONTH(`Date`) = MONTH(CURRENT_DATE()) AND `WorkingDayOfMonth` <= `CurrentWorkingDayofMonth` THEN `Amount`
    WHEN MONTH(`Date`) < MONTH(CURRENT_DATE()) THEN `Amount`
    END
    END

    And then I graph by month. You can also add a variance line by taking (Current Year / Previous Year) -1 

     

    This allows our C-Level execs to see counts by month, this year vs last year, and also see current month through the same working day last year. Including the Variance as your summary for all values will give you the YTD vs LYTD variance as a whole.

     

    Hope that helps get you started. Let me know if you have questions on this,
    Valiant

     

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

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    So the first step is to pair the 'Working Day of Month' to each date in your range. If you don't already have that, here's a table that I use (accounts for major holidays) see attached. 

     

    Join that to your date that you're using. I would also include a constant for CurrentDay and Join to that to get the current working day of month. Once you have that I have 2 separate measures for each of my years in a Year over Year chart (using the grouped bar + line chart type). The current year is just

    CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE()) THEN `Amount` END

    For the previous year I do something like this:

    CASE WHEN YEAR(`Date`) = (YEAR(CURRENT_DATE())-1) 
    THEN CASE WHEN MONTH(`Date`) = MONTH(CURRENT_DATE()) AND `WorkingDayOfMonth` <= `CurrentWorkingDayofMonth` THEN `Amount`
    WHEN MONTH(`Date`) < MONTH(CURRENT_DATE()) THEN `Amount`
    END
    END

    And then I graph by month. You can also add a variance line by taking (Current Year / Previous Year) -1 

     

    This allows our C-Level execs to see counts by month, this year vs last year, and also see current month through the same working day last year. Including the Variance as your summary for all values will give you the YTD vs LYTD variance as a whole.

     

    Hope that helps get you started. Let me know if you have questions on this,
    Valiant

     

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

This discussion has been closed.

Categories