Need to update the logic from a previous discussion

Options

From the solution given in the previous discussions, I want to update the logic to show the average of 5 days a week, and only exclude days that are holidays which land on a weekday

Best Answer

  • david_cunningham
    Answer ✓
    Options

    In order to do that, you would need to fill in the missing data. The best place to do this would most likely be in your ETL combined with a date dimension. This will help you make sure you have data for every single day of the week.

    You can also wrap your column inside of an IFNULL() to replace Nulls with Zero

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • JedP
    JedP Domo Employee
    Options

    Hi @shreeb ,

    You can add a case statement to narrow the data used in the beast mode code. For example, if you want to see Monday - Friday as the 5 days, then you could modify your code like this:

    SUM(SUM(CASE WHEN WEEKDAY(ProductionScheduleDate) BETWEEN 2 AND 6 THEN Orders END) FIXED(BY AssignedTo, ProductionScheduleDate - WEEKDAY(ProductionScheduleDate)))
    /
    AVG(COUNT(DISTINCT CASE WHEN WEEKDAY(ProductionScheduleDate) BETWEEN 2 AND 6 THEN ProductionScheduleDate END) FIXED(BY AssignedTo, ProductionScheduleDate - WEEKDAY(ProductionScheduleDate)))

  • shreeb
    shreeb Member
    Options

    but would that account for holidays ?? @JedP

  • JedP
    JedP Domo Employee
    Options

    Good call out @shreeb , that example does not call out holidays. You can always add in other conditions in the case statement to include the specific holidays. The down side of this method is that you would need to update the code each year with the new holidays.

    Another really common method is to place the date & holiday logic into an ETL to create a column that indicates when the ProductionScheduleDate is a weekend or holiday. This can be done through a mapping table if you have one, or through a formula tile.

    The benefit here would be that you would be able to simplify the case statement to reference the new column. In this example we will call our new column "include dates", and this is how the code would look once the new column is created.

    SUM(SUM(CASE `include dates’ = 1 THEN Orders END) FIXED(BY AssignedTo, ProductionScheduleDate - WEEKDAY(ProductionScheduleDate)))
    /
    AVG(COUNT(DISTINCT CASE `include dates’ = 1 THEN ProductionScheduleDate END) FIXED(BY AssignedTo, ProductionScheduleDate - WEEKDAY(ProductionScheduleDate)))
    
    

  • shreeb
    shreeb Member
    Options

    even if i am adding the holiday condition - since there is no data for a particular day, its taking only those days of the week that has a value.

  • shreeb
    shreeb Member
    Options

    heres an example @JedP -

    6/26 was not a holiday but its not getting considered for that week.

  • shreeb
    shreeb Member
    Options

    So i asked the user, the by default want to find average for the week - total sqft / 5. and only exclude holidays for that week.

  • david_cunningham
    Answer ✓
    Options

    In order to do that, you would need to fill in the missing data. The best place to do this would most likely be in your ETL combined with a date dimension. This will help you make sure you have data for every single day of the week.

    You can also wrap your column inside of an IFNULL() to replace Nulls with Zero

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • shreeb
    shreeb Member
    Options

    @david_cunningham - i tried the ETL with date dimension. Will keep you posted!