Best Of
Re: Set goals in ETL
APPEND your data instead of JOINing it.
Re: Help graphing a calculated field that sums up two data columns
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.
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