Best Of
Re: Waterfall/Retention Ratio in Beast Mode
Hi @JasonKerr ,
I think I need to see it to do more, but your SQL formula looks like it's missing something. So when you write Case when statements you have to have the word "end" at the....end. Here's an example of what is written:
SUM(CASE WHEN `Interview Round` = ‘Second Round’)/SUM(CASE WHEN `Interview Round` = ‘First Round’)
Essentially I think you're trying to do a nested formula within a formula. I'm not sure of the logic you're trying to write so if you maybe give an example I can try to help.
Re: Waterfall/Retention Ratio in Beast Mode
Hi @JasonKerr ,
Thanks for the clarification. Here's a video on the solution: https://loom.com/share/489fb795c735401681896cda6859402e
Issue was that you need a constant. SQL can only do math of a number if another number is on the same row so you have to use an ETL and the Group By function to make that constant.
Take a look and let me know if it works,
John Le
More video solutions at https://www.dashboarddudes.com/pantry
Re: Can someone help me with the syntax?
Hi @damen ,
So you really can't do this because SQL can only look at numbers on the same row. So you'd have to remove that First Mortgage column for your Loan Count Column to finally sum.
I actually just did this for another Domo user: https://www.youtube.com/watch?v=_ihXoDKAeag&list=PLLbgJInsMl7o3pPpLmyVe1AsxwX2OEJGO&index=6
If you REALLY WANT the mortgage amounts to be broken out by mortgage count, you'd need to do that in the group by. You'd have to select date and then sum Monthly Total and Loan counts in the group by.
Anyway, here's a video I made to help you follow along and answer your specific question. Hope it helps: https://www.loom.com/share/a6cf56d544f648bdba296029adb200d8
John Le
More videos at: https://www.dashboarddudes.com/pantry
Re: How do create a formula that add to line items?
Hi @rumoredcs ,
Unfortunately, you can't add numbers really in the way you have data and have date filtering functionality. You'll have to transpose the data using the unpivot functions in the ETL.
Here's a video I made for you to follow along: https://www.loom.com/share/242bc551ed8e4d4c9c2c011b13d499d3
Good luck,
John Le
More video solutions at: https://www.dashboarddudes.com/pantry
Re: Schedule Dashboard To Include CSV Attachments
Hi @DavidSawyers ,
Just to be clear. It sounds like you have 3 tables in a dashboard and you want to send the CSV from each of those as attachments, but in one email. Unfortunately, I don't think you can do that. There are some alternatives I have here so hope that helps: https://www.loom.com/share/7904deb69335442e9e7c1def42d36e60
I honestly would say to just enter their email address and have your client auto receive 3 emails from Domo so you don't have to download and resend anything.
Hope this helps,
John Le
More video solutions at https://www.dashboarddudes.com/pantry
Re: How can you use a relative date filter in the DOMO Campaigns distribution list filter?
Hi @Julie_H ,
So I think you should use a formula trick called "Interval". I answered a previous question around previous dates here: https://www.youtube.com/watch?v=LQZS8NKX-Gg&list=PLLbgJInsMl7o3pPpLmyVe1AsxwX2OEJGO&index=2
So you can saw date - interval 5 day or date- interval 1 year etc
Let me know if this makes sense,
John Le
More video solutions at https://www.dashboarddudes.com/pantry
Re: Formula IFERROR Calculation Error
Hi @quez ,
Happy to help, but I have a clarifying question. If you see 20221201, how do you know if that’s December 1, 2022 or Jan 12, 2022?
Re: Visualization options for minutes:seconds format
@cthtcc If you want do set up a card of a different chart type you could use the fixed decimal field as the value, and then use the formatted column as the data label using tooltips
Re: Line graph - two date ranges
Hi @louiswatson ,
I get this question quite often. So I think you need to combined your dates into one column. Here's a video I made showing you two different ways to do it depending on your dataset. I wasn't sure so I had to guess a bit:
https://www.loom.com/share/3ac1b9a4c13a4da58ff0b6d32e152881
Good luck,
John Le of Dashboard Dudes
You're only one dashboard away. More video solutions at https://www.dashboarddudes.com/pantry
Re: Trimming and converting a Date/Time Field
You can use the HOUR function to return a value between 0 and 23 to filter on (in your case 9-17).
CASE WHEN HOUR(`Timestamp`) BETWEEN 9 AND 17 THEN 'Keep' ELSE 'Exclude' END
If you want to convert the times in a 12 hour format you can use the DATE_FORMAT function with a specific format string
DATE_FORMAT(`Timestamp`, '%Y-%m-%d %r')
%Y - 4 digit year
%m - 2 digit month
%d - 2 digit day
%r - Time, 12-hour (hh:mm:ss followed by AM or PM)
For more information on the different formatting options for the format string you can reference https://domo-support.domo.com/s/article/360043429953?language=en_US
