2 Replies Latest reply on May 18, 2016 3:39 PM by Bridget Cogley

    Dynamic holiday calculations

    Kimberly Sisco

      Hello everyone!

       

      I'm using the following formula to calculate the number of holidays between two dates, and it works:

      (SUM(IF #12/25/2015# >= ["Start Date"] AND #12/25/2015# <=["Stop Date"] THEN 1 ELSE 0 END) + //Christmas

      SUM(IF #01/01/2016# >= ["Start Date"] AND #01/01/2016# <=["Stop Date"] THEN 1 ELSE 0 END) + //New Year's

      SUM(IF #05/23/2016# >= ["Start Date"] AND #05/23/2016# <=["Stop Date"] THEN 1 ELSE 0 END) + //Memorial Day

      SUM(IF #07/04/2016# >= ["Start Date"] AND #07/04/2016# <=["Stop Date"] THEN 1 ELSE 0 END) + //Independence Day

      SUM(IF #09/05/2016# >= ["Start Date"] AND #09/05/2016# <=["Stop Date"] THEN 1 ELSE 0 END) + //Labor Day

      SUM(IF #11/24/2016# >= ["Start Date"] AND #11/24/2016# <=["Stop Date"] THEN 1 ELSE 0 END) + //Thanksgiving

      SUM(IF #12/25/2016# >= ["Start Date"] AND #12/25/2016# <=["Stop Date"] THEN 1 ELSE 0 END) + //Christmas

      )

      (I actually have all the holiday dates for 2012 - 2020 in this calculation, I just didn't feel it was necessary to post the whole thing)

       

      Let's say I get some older data loaded, or I forget to add more dates and this dashboard is still alive after 2020.

      I've been trying to get the calculation more dynamic, and what I have below is what I have came up with so far.  It does not account for the Start Date/End Date being in different years, however.  Anyone have any suggestions?

       

          (

          //New Year's Day

           SUM(IF (DATE("1/1/" +Str(DATEPART('year',["START DATE"])))) >= ["START DATE"]

           AND (DATE("1/1/" +Str(DATEPART('year',["START DATE"])))) <=["END DATE"]

           THEN 1 ELSE 0 END)

          +

          //Memorial Day

           SUM(IF (DATETIME(STR(YEAR(["START DATE"]))+"-6-1") +

           IF DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-6-1")) > 2

           THEN 9 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-6-1"))

           ELSE 2 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-6-1"))

           END - 7) >= ["START DATE"]

           AND (DATETIME(STR(YEAR(["START DATE"]))+"-6-1") +

           IF DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-6-1")) > 2

           THEN 9 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-6-1"))

           ELSE 2 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-6-1"))

           END - 7) <=["END DATE"]

           THEN 1 ELSE 0 END)

          +

          //Independence Day

           SUM(IF (DATETIME(STR(YEAR(["START DATE"]))+"-7-4")) >= ["START DATE"]

           AND (DATETIME(STR(YEAR(["START DATE"]))+"-7-4")) <=["END DATE"]

           THEN 1 ELSE 0 END)

          +

          //Labor Day

           SUM(IF (DATETIME(STR(YEAR(["START DATE"]))+"-9-1")+

           IF DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-9-1")) > 2

           THEN 9 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-9-1"))

           ELSE 2 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-9-1"))

           END) >= ["START DATE"]

           AND (DATETIME(STR(YEAR(["START DATE"]))+"-9-1")+

           IF DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-9-1")) > 2

           THEN 9 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-9-1"))

           ELSE 2 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-9-1"))

           END) <=["END DATE"]

           THEN 1 ELSE 0 END)

          +

          //Thanksgiving

           SUM(IF (DATETIME(STR(YEAR(["START DATE"]))+"-11-22") +

           IF DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-11-22")) > 5

           THEN 12 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-11-22"))

           ELSE  5 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-11-22"))

           END) >= ["START DATE"]

           AND (DATETIME(STR(YEAR(["START DATE"]))+"-11-22") +

           IF DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-11-22")) > 5

           THEN 12 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-11-22"))

           ELSE  5 - DATEPART('weekday', DATE(STR(YEAR(["START DATE"]))+"-11-22"))

           END) <=["END DATE"]

           THEN 1 ELSE 0 END)

          +

          //Christmas

           SUM(IF (DATETIME(STR(YEAR(["START DATE"]))+"-12-25")) >= ["START DATE"]

           AND (DATETIME(STR(YEAR(["START DATE"]))+"-12-25")) <=["END DATE"]

           THEN 1 ELSE 0 END)   

          )

      TIA!

        • 1. Re: Dynamic holiday calculations
          Bridget Cogley

          Hello Kimberly

           

          Not sure if you ever got this resolved, but I did work up a fully automated solution.  It would need validated, but on SuperStore data, it works.

           

          Here's what I did:

          1. Created a custom date off my date field (Order Date in this case) to have month and year only.  You can also trim this down to just year to conserve calculation power.  You will need this as a field in order for later calculations to work.  This is the _0 Order Date Field.  If you already have a year field, use that.
          2. Made a 'First of the Month' calculation.  There's several ways to do this; I picked one - if you already have one for your data, then use that.  This is _1 First of the Month.
          3. Made the monster holiday calculation.  I annotated this as much as possible.  If you're familiar with Level of Detail (LOD) expressions, you can delete a chunk of my notes.  If not, they attempt to guide through those specific calculations.  I did it for the holidays in your example, but based on that, you should have a good way to do any other holiday.  It's the _3 Holiday field.  I marked them as 'Holiday' so that this calculation could be used a few different ways, including counting.  You can name them the holiday name and do a COUNTD, or a COUNTD on dates for holiday.
          4. Lastly, for the visual, I created the _4 Week in Month to ensure this was working as intended.

           

          Let me know if that helps and sorry for the significant delay.

           

          Bridget

          • 2. Re: Dynamic holiday calculations
            Bridget Cogley

            And it looks like it detached the workbook....grrr!

             

            Rd. 2