Best Of
Re: CBSA Mapping (Census Core-Based Statistical Area)
Scott,
One thing you can do is to use this FIPS CBSA crosswalk on this page.
Ideally, you could find a svg files that has the excact CBSA borders, but one didn't turn up for me in a quick google search.
Hope this helps.
Re: How to create a stacked bar chart?
Go to the "Vertical Bar" section in Chart Types → Select "Bar" and then add a series to it and it will be a stacked bar!
Re: How to create a stacked bar chart?
@muahmmad_zaidi The stacked bar chart type is listed under "Bar", and is the first option listed under Popular Charts
Re: Build a Data Time field from columns containing date, hour, minute, second
I figured it out… here is the SQL for anyone else trying to do the same:
STR_TO_DATE(CONCAT(DATE_FORMAT(Date Updated, '%b %e, %Y'), ' ', TIME_FORMAT(SEC_TO_TIME(Time Updated), '%h:%i:%s %p')), '%b %e, %Y %h:%i:%s %p')
swagner
Re: GA4 campaign and session campaign data Joining question
@jakebutterfield You are correct that nulls are populating after the join because the right table has fewer unique values in the Campaign field than the Session Campaign field in the left table. Left joins return all rows from the table on the left, regardless of whether it matches any rows in the right table. If a match is not found, then the output columns from the right table will be null.
I'm not familiar with Google Analytics data, but I would suggest reviewing your connector settings and upstream dataflows to make sure there are no other reasons why there are so fewer "Campaign" values than "Session Campaign"
Re: Calculating a final value from two selected values that exist in the same column?
A less elegant way which I had to use in the past, that might come in handy in your case (supposing the quarter is an actual value and not dates that you're having Domo aggregate to a specific quarter, in which case you'll need to deal with it).
So my sample dataset has 2 columns as you mentioned, Revenue and Collected Quarter, I even have multiple entries on each quarter to show that you can do the aggregation at the Beast Mode level:
Just for comparison we can get the total revenue per quarter
From here we trick the system and use a Single value card, with some specific settings, like making the quarters a quick filter, so users can select which ones to compare, limiting the results to show a single line, and sorting on the Collected quarter descending (so we always get the difference from the latest quarter to the newest, this will depend on your needs)
So your card will look something like:
Here the Beast Mode for Difference is defined as:
CASE WHEN (SUM(COUNT(DISTINCT Collected Q)) FIXED ()) != 2 THEN 'Select exactly 2 quarters' ELSE SUM(Revenue) - LAG(SUM(Revenue),1) OVER (ORDER BY Collected Q ASC) END
The first case statement makes sure that we're only showing 2 quarters and that if you have more than or less than that you get a nice error message that asks people to select exactly 2. Then once we know we only have 2 quarters we get the difference between the SUM for the current row minus the SUM of the previous one (obtained using the lag function).
Tada!!! you have a way to do what you wanted without additional ETLs or datasets, just in a very weird and odd way 🤣
Re: Date format question
@Aag2023 What do the valid date values look like in Domo? You will likely need to use a STR_TO_DATE() function to parse it into a date field yourself
Re: Group by when using case statements?
@user046467 can you post a sample of what the dataset you have looks like? As Grant said, you can do a lot with Windows functions (or the Fixed function) but there are limitations for which pre-aggregating needs to happen at a ETL level, but without fully understanding what your data looks like, we're mostly guessing in the dark. (I fear there might be an easy answer we might be overlooking due to lack of insights on the data)