GrantSmith Coach

Try replacing "Remove" with 'Remove' as the double quotes can indicate a column name and not a specific value What specifically is it returning? Is it just dropping Dummy from your dataset?

How far back does your data go? If you don't have any data prior to 2020-Q4 it'll return NULL because there are no prior rows for it to pull from. When you have a NULL with a simple arithmetic operator (=, -, *, /) the entire result ends up being NULL. You can wrap your entire LAG...OVER() in a COALESCE function and…

Hi @DomoNewbie Have you read my prior article on rolling averages using window functions? This may help you out: https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest

Hi @AnwarBham That level of detail doesn’t exist in the governance/metadata tables and isn’t easily available without getting very technical with some API calls

Hi @Eli_Rollins I've done a writeup in the past about custom date offsets which will likely do what you're looking for. You can have a date and then have different offsets within that dataset. https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest I'll also be presenting on…

Hi @JoeR_PSIT It doesn't appear there's currently an Azure AD connector that's been written. There is an API that Azure AD offers for some reporting on sign-ins and audit logs which is outlined on Microsoft's website: https://docs.microsoft.com/en-us/azure/active-directory/reports-monitoring/concept-reporting-api You have…

Hi @user048760 I don't know how you're calculating the difference for days past the auction but you can use something like this and replace `days past` with your formula or column that holds the number of days past. CASE WHEN `days past` >= 90 THEN '90+' WHEN `days past` >= 60 then '60-89' WHEN `days past` >= 30 THEN…

Hi @CarolineRogg1 One tip you can do is perform your case statement in descending order. This is because a case statement will execute the first case it comes across that's true so it's implied when doing it in decending order that it's already less than the prior value. CASE WHEN `Age (Hours)`>= 144 THEN '> 144' WHEN `Age…

Hi @swagner , The fastest option is to indeed stop the Domo Workbench service. This will prevent new jobs from being added to the queue to be run. I've run into issues like this in the past where jobs will be stuck in a "running" state on workbench but the job will have completed in Domo itself. When telling a job to…

Hi @eriena You'll want to utilize a recursive dataflow. The knowledge base has several different articles on how to do this. Here's the Magic ETL version: https://domohelp.domo.com/hc/en-us/articles/360057087393-Creating-a-Recursive-Snapshot-DataFlow-in-the-New-Magic-ETL

You may also want to look into PDP Policy Autocreation: https://domohelp.domo.com/hc/en-us/articles/360043439353-PDP-Policy-Autocreation Talk with your CSM to get the jar file.

CASE WHEN `A.a` = `B.a` AND `A.b` = `B.b` THEN CASE WHEN `A.c` = `B.c` AND `A.d` = `B.d` THEN 1 ELSE 0 END END This will return NULL if A.a and A.b do not equal B.a and B.b resepctively. 1 if c and d match and a and b match 0 if they don't but a and b match

Looks like @MarkSnodgrass is a faster typer than me. Gotta work on my stealthy quickness.

Hi @Pierce13 When I'm doing period over period comparisons I'll set up a custom date dimension with offsets. This way you can control how your dates are being compared and makes it a little easier and more manageable to calculate metrics like you're wanting. You can read up on this process here:…

00:02:00 is 2 minutes, not 2 hours. Have you tried wrapping your seconds in a SUM function before passing to SEC_TO_TIME? SEC_TO_TIME(SUM(`total_seconds`))

Hi @loganherzog You're likely correct in that there's too many values for it to find just '8'. If it's a numerical column you can use a Custom Filter PDP and just state Store No between 8 and 8 to do a hacked equals equation. Alternatively you could format your numbers as 3 digit (assuming the highest store number you have…

Hours: - Divide the total seconds by the number of seconds in an hour (3600) and drop the remainder (FLOOR) FLOOR(`total_seconds` / 3600) Minutes - Get the remainder seconds after converting to hours then divide that by 60 to convert to minutes. Floor to drop the remainder FLOOR(MOD(`total_seconds`, 3600) / 60) Seconds: -…

Hi @Stefan Domo provides some metadata specifically with this information. If you use the Domo Governance DataSets connector you can pull in the "Dataset Schema" dataset which will list all of the datasets and columns in each dataset.

Hi @micheleb you can use SEC_TO_TIME to take the number of seconds and format it with hours:minutes:seconds. I’ve done another write up of custom timespan difference here: https://dojo.domo.com/discussion/52682/domo-ideas-conference-beast-modes-time-difference-formatting#latest

Hi @cpbwg Typically when I’m dealing with nested data in BigQuery I will unnest the data first as it’s fairly difficult to have Domo do the unnesting. Domo’s architecture expects things to be in a flat tabular format for processing. You can always group your data and then combine values together with a comma using the…

Hi @daniel_cruz_2016 This currently isn't an option. I'd recommend adding it as a idea to the idea exchange.

Addendum Here's a corrected / updated Magic ETL implementation as the prior version had some incorrect offset values. {"contentType":"domo/dataflow-actions","data":[{"name":"Domo Dimensions -…

Hi @user012068 What format is your data in? Is it already aggregated on a monthly basis or is it daily? You'll want to make sure your data is aggregated on a monthly basis and you can then utilize the LAG window function to pull the values for the past 18 months for each month. LAG(SUM(`patient_count`), 0) OVER (PARTITION…

I'm not aware of one that's broken out per state in a single dataset however some states websites may have this information. You could also search through data.gov which has some state level datasets. https://catalog.data.gov/dataset

Hi @micheleb Are you sorting your results on your card based on the the aggregated / SUM of your values?

Make sure you're using the correct layout id. Also you may need to do a PUT request to the writelock endpoint (https://[INSTANCE].domo.com/api/content/v3/pages/layouts/[LAYOUT_ID]/writelock ) to establish a write lock before attempting to save your layout and then issue a DELETE request to the same writelock endpoint to…

You can use the page layout API to get the definition and to also save the updated definition. https://[INSTANCE].domo.com/api/content/v4/pages/layouts/[PAGEID] use a GET request to get the definition and a PUT request (with the JSON definition as the body of your request) to save the updated definition Note: This is not…

Hi @user048760 You can utilize a beast mode to conditionally add the values together with a CASE statement: SUM(CASE WHEN `Marketing Channel` IN ('Yelp', 'Facebook', 'Google Ads') THEN `Value` ELSE 0 END)

@faithscent Domo now offers a new product called "Sandbox" which does what you're looking for where you can promote pages to "production". You can talk with your CSM to get a demo of it. It is a premium feature so it'll be an extra charge. You can read more about it here:…

Hi @LCash Just curious if you multiply your numerator and denominator by 1.0 to force them both to be decimals on the off chance that they were being treated as integers and doing integer division instead of decimal / float division. (SUM(column1) * 1.0) / (COUNT(column2) * 1.0)