GrantSmith Coach

Hi @Yogesh You can utilize SQL INTERVALs to do this. `CREATED_ON` > CURDATETIME() - INTERVAL '1' HOUR Here's a website that talks about them in more detail and lists all of the possible options for the units: https://www.mysqltutorial.org/mysql-interval/

Hi @user060040 Without seeing how your data is structured it's difficult to give an answer to your question as the format will dictate how you can solve this problem. Could you also post some pseudo data of how your dataset is structured? Is it structured like: LOBRequest TypeYearCount Or LOBRequest Type A CountRequest…

Hi @user060355 Without seeing your data - How is your budget structured? Is that a yearly, monthly or daily budget? You're likely comparing apples to oranges in terms of your unit of measurement. The numerator is likely on a daily basis but your budget is probably on a non-daily scale. You'll want to either utilize a MIN…

Hi @user056039 @Wills is referring to Windowing Functions. This is a feature switch you can talk to your CSM to get it enabled within your instance. Here's a webpage describing windowing functions.

Hi @Khan_Naziya You won't have visibility into the workbench job schedules via a governance dataset. Workbench jobs (including their schedules) can be exported from workbench using the wb.exe executable. wb.exe export-jobs --help That will give you usage details where you can export your jobs to a folder in JSON notation…

Hi @Khan_Naziya , You'd need to connect via an API Access Token and use the -t flag instead of -u and -p. You can generate a token under More > Admin Settings > Security > Access Tokens (assuming you have permissions to view / create those - if not talk to your Domo admin) You'd use that token that's generated as the -t…

Hi @user32470 , You'd need to have two records for each competitor in your data set. Assuming your data looks like this: DateCompetitorAmount1/1/2020ABC5001/1/2020DEF1231/1/2020Our Company987 You'd want it structured like: DateCompetitorComparisonAmount1/1/2020ABCThem5001/1/2020ABCUs9871/1/2020DEFThem1231/1/2020DEFUs987…

Hi @Crisocir You can do this utilizing a window function (If you don't have it in your instance you'd need to talk to your CSM to get it enabled). Essentially this beast mode is calculating the row numbers (within each invoice) and then checking to see if it's the first row, if so return the tax amount otherwise return 0.…

Hi @user07592, You could utilize a dataflow and append the datasets together (assuming they have the same schema) to then create an output dataset. Afterwards you could delete the dataflow as well if you're not interested in keeping it or the 7 input datasets around.

Hi @user013269 You'd need to format them as a string in the format you wish using your query before you pull it into Domo.

Hi @user057613 #1 is possible with a lengthy beast mode. #2 is not possible. Text cards don't support HTML markup. Only within table cards. CONCAT(CASE WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 12 THEN CONCAT( SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 3), ',', SUBSTRING(ROUND(SUM(`Number Orders`), 0), 4, 3), ',',…

Hi @user093540 There isn't a simple way to do it but you'd have to add the cards to your dashboard and then move them back to the appendix in the order you want them in.

Instead of using COUNT try using SUM: SUM(Condition1)/ sum(count(distinct ID)) over (PARTITION BY `ID`)SUM(Condition2)/ sum(count(distinct ID)) over (PARTITION BY `ID`)SUM(Condition3)/ sum(count(distinct ID)) over (PARTITION BY `ID`)SUM(Condition4)/ sum(count(distinct ID)) over (PARTITION BY `ID`)

Hi @user016969 You're close but instead of using a window across the entire dataset you're wanting it across each ID / Name. Just a minor change to your beast mode should get you what you want. Count(Condition)/ sum(count(distinct ID) over (PARTITION BY `ID`)

Hi @user045611 If you're receiving text and app notifications - have you checked your spam folder?

Hi @user057613 You can utilize a beast mode to calculate the % change. (SUM(`Current Value`) - SUM(`Prior Value`))/SUM(`Prior Value`) (Make sure to format this number as a percentage) If you want to combine that with the total amount you can utilize the CONCAT function and do some % formatting. CONCAT(SUM(`Current…

Hi @EM You can utilize DAYOFWEEK and some simple math to get the last day of the week. `Date` + INTERVAL (7 - DAYOFWEEK(`Date`)) DAY The DAYOFWEEK function returns a number between 1 (Sunday) and 7 (Saturday). Subtracting that from 7 gives us the number of days until the end of the week which we just add that number of…

Hi @tstimple There's two options you have. The first is to create 50 beast modes one for each state using a like statement which is not ideal and a nightmare to maintain. The better option is to preprocess your data and split out so you have one record for each state. You can utilize a split column tile to split each state…

Hi @user066906 , It's likely not your join. With the changes in Magic ETL 2.0 NULLs are handled differently than in ETL 1.0. With 2.0 they are now handled the same way as SQL, as a special value. ETL 1.0: NULL <> 'Delete' = TRUE ETL 2.0: NULL <> 'Delete' = FALSE This is because you must use an explicit NULL check instead…

Hi @HashBrowns Try this (I don't have your exact data set to test so may not exactly work): SUM(COUNT(DISTINCT `lte_donation_general.donation_id`)) over (partition by `lte_donation_general.collection_date`,`lte_tbl_calendar.location_name_tzw`) You're essentially trying to calculate the number of unique donations for each…

Hi @user056607 When subtracting two dates in MySQL it should return a single number representing the day difference. So you can try something like: `Cancellation Date` - `Publish Date` <= 90 Alternatively you can utilize UNIX_TIMESTAMP to calculate the number of seconds since 1970-01-01 and then calculate the second…

Hi @JPeG You could create a beast mode to extract only the date parts you're interested in. CONCAT(LEFT(MONTHNAME(`Date`), 3), ' ', DAY(`Date`)) Left is just making a 3-letter month abbreviation of the name since MONTHNAME returns the entire name. Because this is now a string it will sort alphabetically rather than by…

@nj-John-mirc Interestingly enough the formula STR_TO_DATE is using a different formatting character set than the Beast Mode does. Instead of %-b you want %e. For a reference on the formatting characters you can look at https://www.mysqltutorial.org/mysql-date_format/ STR_TO_DATE(`Date As String`, '%e %b')

Are you doing this in a beast mode or in a query on your database bringing the data in (if so which DB type)? Do your dates actually include / or is that just from the error output text? Utilizing STR_TO_DATE in a beast mode seems to work just fine for me.

@nj-John-mirc How does it not work for the double digits? What's the formula you're currently using? %-b should handle single and double digit day numbers.

Hi @Crisocir Domo does have an Activity Log you can try and reference. I utilize it for some governance cards I have to determine when a card was last viewed. I inner join (to get only card activity information) the Activity Log dataset to the Cards (Third Party Data Governance dataset - could use DomoStats datset variant…

Try using %-d instead of %d. %dDay of the month as a zero-padded decimal number.03%-dDay of the month as a decimal number. (Platform specific)3

Hi @nj-John-mirc Have you tried concatenating the appropriate year onto your string and then using STR_TO_DATE? STR_TO_DATE(CONCAT(`Date`, ' ', YEAR(CURRENT_DATE)), '%d %b %Y') How is the STR_TO_DATE function not working? Does using the format string '%d %b' work in STR_TO_DATE (without concatenating the year)?

Hi @MartinB @What you’re wanting is a windowing function with a partition because it sounds like you only want to calculate the information within each specific group. I'm not exactly clear on how you're wanting to process your data but it sounds like you're wanting to calculate the total quantity that item has of the…

Hi @user02700 You can add the number of hours to your date using an interval. This will make it a DateTime instead of a Date. `Date` + INTERVAL `Hour` HOUR