Best Of

lag(sum(case when `control or target` = 'target' then `vol_usd` else 0 end)) over (partition by year(`trade_date`)*100 + month(`trade_date`))

I think Domo broke / changed functionality.

You can no longer use an expression in the partition or order by clause -- year(`trade_date`)*100 + month(`trade_date`)

add this to your dataset as a hardcoded column

@AJ2020 You're welcome!

If you want to change the number of business days this is the template you'd can use. Just change [BUSINESS DAYS] to the number of business days you're wanting to add.

DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR([BUSINESS DAYS]/5) * 7 + CASE WHEN MOD([BUSINESS DAYS],5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + MOD([BUSINESS DAYS],5)) DAY)

Hi @AJ2020

MOD is the modulo operation. It simply says "divide these two numbers together and give me the remainder". In other words, how many left over days do I have after calculating the number of full weeks.

FLOOR just returns the whole number of a division operation without the fraction. I'm using it in two places. The first FLOOR([DAYS]/5) is telling me the number of whole "business" weeks I'm calculating to multiply by 7 to convert them to actual weeks. The other is just telling me if the start day is a Saturday (7) and if so subtract 1 for a one day weekend instead of 2.

It needed to add the number of business days remaining. My apologies. Currently it was only ever adding a single business day (+ 1) and not the day making up the partial week.

-- https://dojo.domo.com/discussion/52342/business-days-in-date-add-interval#latest
-- Logic:
-- Calculate the number of full business weeks
-- Convert to actual 7 day work weeks
-- Determine if adding the remining business days will go over a weekend
-- if so, add 2 (or 1 if starting on a Saturday) to "jump" the weekend.
-- Add the remaining business days.

-- New after 3 PM
CASE WHEN (`PurposeType`= 'New') AND (HOUR(`StartDateTime`)>=15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(2/5) * 7 + CASE WHEN MOD(2,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + MOD(2,5)) DAY))

-- New before 3 PM
WHEN (`PurposeType`= 'New') AND (HOUR(`StartDateTime`)<15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(1/5) * 7 + CASE WHEN MOD(1,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + MOD(1,5)) DAY))

-- Renew after 3 PM
  WHEN (`PurposeType`!= 'New') AND (HOUR(`StartDateTime`)>=15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(4/5) * 7 + CASE WHEN MOD(4,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + MOD(4,5)) DAY))

-- Renew before 3 PM
WHEN (`PurposeType`!= 'New') AND (HOUR(`StartDateTime`)<15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL
-- Number of Full Weeks that make up the number of business days -> convert to actual 7 day weeks
(FLOOR(3/5) * 7 +
-- MOD tells us the remainder of business days which don't make up a full business week
-- Does this go over a weekend?
 -- if so, add 2 (or 1 if starting on a Saturday) to "jump" the weekend.
CASE WHEN MOD(3,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END
-- Add the remaining business days
 + MOD(3,5)) DAY))

ELSE ' '

END

Ahh, I see that now in the sample data I mocked up. When I changed the beast mode from

(`Lines on Time`) / (`Number of Lines`)

to

SUM(`Lines on Time`) / SUM(`Number of Lines`)

then the average aggregation works properly.

Give that a try and see how it works for you.

Hi @AJ2020

This was a fun question that I had to play around with. Currently you can't just say "add X business days" to the date_add function (though it'd be great). The following beast mode will add X number of business days to a date however it's not smart enough to exclude holidays.

Here's the basic version of it replace all instances of 6 with the number of business days :

`dt` + INTERVAL (FLOOR(6/5) * 7 + CASE WHEN MOD(6,5) + DAYOFWEEK(`dt`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`dt`)/7) ELSE 0 END + 1) DAY

dt is your date column.

FLOOR(6/5) *7

Is determining the number of full weeks for the number of business days

CASE WHEN MOD(6,5) + DAYOFWEEK(`dt`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`dt`)/7) ELSE 0 END

Is determining if the partial week would overlap Saturday (7) / the weekend and then subtract a day if we're starting on Saturday (1 day weekend and not 2 day weekend)

And finally we add 1 to correctly offset the addition of the days.

This makes it a bit more complex logically but simpler in trying to calculate the number of actual days from the business days. You don't have to pre-calculate the number of actual days based on your business dates.

To put a nice bow on your request:

CASE WHEN (`PurposeType`= 'New') AND (HOUR(`StartDateTime`)>=15) THEN DATE(DATE_ADD(`StartDateTime`), INTERVAL (FLOOR(2/5) * 7 + CASE WHEN MOD(2,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + 1) DAY) --New after 3 PM

WHEN (`PurposeType`= 'New') AND (HOUR(`StartDateTime`)<15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(1/5) * 7 + CASE WHEN MOD(1,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + 1) DAY)) --New before 3 PM

  WHEN (`PurposeType`!= 'New') AND (HOUR(`StartDateTime`)>=15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(4/5) * 7 + CASE WHEN MOD(4,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + 1) DAY)) --Renew after 3 PM

WHEN (`PurposeType`!= 'New') AND (HOUR(`StartDateTime`)<15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(3/5) * 7 + CASE WHEN MOD(3,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + 1) DAY)) --Renew before 3 PM

ELSE ' '

END

Hi @user077529

You can utilize a Group By tile in a Magic ETL dataflow to group your rows together and then use the "Combine strings by ," option in the group by tile.