Best Of

If your beast mode is calculating correctly in the pivot table, you can use that same beast mode as the y axis in the bar graph and the sales date in the x-axis and then use the date range filter in the top right to tell it to graph by month.

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.