Best Of
Re: Fill down values in a column in DOMO ETL
to fill down data. for each column you want to track each time the row changes.
so you need
Prev_Value (use a LAG 1)
then is_Change (case when Prev_Value != value then 1 then 0
then cum_sum of isChange
in your previous example you should have 1 all the way from Text 1 to Text 2, then from Text 2 onward should be a 2 up until Text 3...
at this point you can JOIN (SELECT * FROM ... WHERE is_change = 1) to your BaseTable
On cum_sum = cum_sum. this will spread VALUE across each row in BaseTable for each cum_sum group.
Then rise wash and repeat for each column. This solution will be easiest in Magic 2.0 or Redshift but probably not in MySQL.
If you're doing it in MySQL you actually can do this with fewer steps user-defined variables and a case that matches on if the value changed or not.
Re: WB5 time settings
There doesn't appear to be a way to choose your timezone. However, if you click on Advanced Schedule, It will show the time in the timezone your computer is set to next to the UTC time that you can edit.
Re: Lag Window Functions gave me split rows
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
Re: Cannot Move page to subpage with Admin Access
I have struggled with this at times as well. I assume you are trying to move these pages through Admin - Pages. What is not immediately clear when you are viewing the pages listed on this page is that it doesn't show you all the pages just by scrolling. In order to move pages not initially shown, you need to find them by using the filter feature.
As shown, click on the 3 dots and then start typing the name of the page and then click Apply. You should see your pages that you are looking for.
Let me know if this fixes your issue or if you have something else going on.
Re: Is there any way to do an UPSERT with data coming from Postgres?
I just finished your tutorial video, and it is fantastic. You explained more in 15 minutes than I've been able to figure out by hunting through the docs and such for hours. Thanks! Highly recommended.
Re: Business days in Date_Add Interval
@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)
Re: Business days in Date_Add Interval
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
Re: Adjustable % column
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.
Re: Business days in Date_Add Interval
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