GrantSmith Coach

Get the left characters up to the length of the last 5 characters. LEFT(`Field`, LENGTH(`Field`)-5)

Glad you figured it out. For those who may come later this was because 'en-ca' was being used as a string in a logical expression. It should use an IN expression or multiple logical expressions to compare for both 'en-us' or 'en-ca': CASE WHEN RIGHT(TRIM(`URL`),5) IN ('en-us', 'en-ca') THEN 'Yes' ELSE 'No' END

You can view the save comments on the Version tab of the dataflow's page. There isn't a governance dataset that contains the information though.

You can use a window function in an ETL to calculate the proximity rank where you partition based on your store and order by the proximity distance in ascending order then in your card you can filter for where the rank is <= 5 Alternatively you can try and do the same thing with a beast mode on the card: SUM(SUM(1)) OVER…

You can use a case statement within your SUM aggregation: SUM(CASE WHEN [CONDITION HERE] THEN `Quantity` END)

It may seem a bit complex but because you're wanting to get YoY for dates other than this year this is the best option.

Wrap your beast mode in a case statement to check for this year and then another to check for last year This Year Count: CASE WHEN YEAR(`Fiscal Date`) = YEAR(CURDATE()) THEN COUNT(`Field`) END Last Year Count: CASE WHEN YEAR(`Fiscal Date`) = YEAR(CURDATE())-1 THEN COUNT(`Field`) END You can then put both of these beast…

Logger is a python builtin logging.logger object. You can read the documentation on that object here: https://docs.python.org/3/library/logging.html transport is an instance of DomoAPITransport object: https://github.com/domoinc/domo-python-sdk/blob/dacdee87d9e798f4c83f270b38c8ea2b1b6c7923/pydomo/Transport.py#L8 You need…

What you'll want to do then since you want historical values you can configure a date dimension dataset to then compare the data to itself with a specific offset (in your case the year). I've done write ups on how to do this here:…

Ok, you can utilize a Magic ETL and the Rank & Window tile to calculate the customer's order number. Make sure you sort on the order date and partition based on the customer identifier and calculate the row number. This will then get you a number for each customer's order with their first order having a value of 1. You can…

Did you have users get created or removed during the month? For example if we're at 50 but you removed 5 users and added 5 users Week 1: 50 users Week 2: 50 users - 5 removed, 5 added = 50 Week 3: 50 users - 5 removed, 5 added = 50 At the end of the month you'd have 60 different users instead of the 50 you're seeing each…

Hi @zuchu Do you have the customer's first order date as part of your dataset or do you need it calculated?

Yesterday: CASE WHEN `INVOICE_DATE` = CURDATE() - INTERVAL 1 DAY THEN `INVOICE_TOTAL_AMOUNT` END Last Year: CASE WHEN `INVOICE_DATE` = CURDATE() - INTERVAL 1 YEAR THEN `INVOICE_TOTAL_AMOUNT` END

You'll want to do something like: ( AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE()) THEN `purchase_price` END) - AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE())-1 THEN `purchase_price` END) ) / AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE())-1 THEN `purchase_price` END)

What you could do is use a recursive ETL to store the current value that's being updated and the old value that was in the former dataset. Then compare the two, if they're different you can set a flag on the record saying it changed and then alert on that specific value.

Have you looked into subtotals on the table card and having your red/green column be your subgroupings?

So are you just looking to see if the most recent file received date comes after the encounter date for each patient?

There's been a few more fixes which have been pushed out. If you're still having the issue I'd recommend logging a ticket with Support about the issue.

What you might need to do is query your dataset to see what transaction dates were updated during the last partition chunk of insertion date and then re-pull the all of the transactions with the transaction dates from the list of transactions dates which were inserted / updated. So you're partitioning based off the…

Using a partition connection will allow you to pull in a subset of the data that gets updated instead of having to pull all of the data which would reduce the amount of data needed to process and thus improving the import speed into Domo.

I'd recommend reaching out to Domo Support as this sounds like a visualization bug that the development team might need to take a look at.

Are you sorting first on the unaggregated date filed and then sorting on the unaggregated sort order beast mode you wrote?

You can use a Group By tile in your ETL for each encounter date and location to get the min or max date instead of making it a comma separated list of strings. You can then compare to the maximum date instead of all the dates. You'll need to join your group back to your original dataset based on your grouping fields to put…

This example was deployed to the domo-dojo instance for reference. https://domo-dojo.domo.com/page/1880852133 If you need access you can refer to https://dojo.domo.com/main/discussion/55204/access-to-domo-dojo-instance

You'll want to make sure you have all of the data for the month for each update as it's going to replace the old version of the month data. Instead of joining on a date you'll want to calculate the month (you can use YEAR and MONTH to calculate it or just LAST_DAY on your date to get the month date) and join on that month…

What I'd recommend is establishing a new date dimension dataset which has each of your fiscal week values for each of the dates, you can then join this to your dataset to have the correct fiscal week number

Have you looked into the new Variables that Domo just deployed to dynamically change your values? https://domohelp.domo.com/hc/en-us/articles/7903767835031-Variables-Overview

The batch_last_run will be the value when your raw data is imported into the system, the currentdate in an ETL would be populated when the ETL actually runs. Depends on what you're wanting to track with your date.

You could use it as your partition key but if you're only wanting a single entry you'll want to convert it to a date first before using it as your partition key.

Use a group by tile to group on the name / ID and then take the maximum of your date field.