Need to update the logic from a previous discussion

shreeb · June 26

https://community-forums.domo.com/main/discussion/65604/average-question

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

david_cunningham · June 27

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

JedP · June 26

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 · June 26

but would that account for holidays ?? @JedP

JedP · June 26

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 · June 26

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 · June 26

heres an example @JedP -

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

shreeb · June 27

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 · June 27

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

shreeb · June 27

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

Need to update the logic from a previous discussion

Best Answer

Answers

Categories