Best Of
Re: Remove duplicate unknown comma separated values within a single 'cell'
Here's a screenshot with an example (it's using bogus data but illustrates what I'm talking about):
Re: Smart Text - Show Actual Year Value, Rather Than Date Range
@drewswayne awesome. Also, you can hide the title on any card on a dashboard by putting the dashboard in edit mode, clicking the "edit content" option on the card, choosing "appearance", and deselecting "title".
jessdoe
Re: Calculate Previous Quarter Start and End Date
These are untested but something like this should get you what you're looking for:
Previous Quarter Start Date
LAST_DAY(`Report Date`) - INTERVAL (MOD(MONTH(`Report Date`), 3) + 3) MONTH + INTERVAL 1 DAY
Previous Quarter End Date
LAST_DAY(`Report Date`) - INTERVAL (MOD(MONTH(`Report Date`), 3)) MONTH
Previous Two Quarter Start Date
LAST_DAY(`Report Date`) - INTERVAL (MOD(MONTH(`Report Date`), 3) + 6) MONTH + INTERVAL 1 DAY
Previous Two Quarter End Date
LAST_DAY(`Report Date`) - INTERVAL (MOD(MONTH(`Report Date`), 3) + 3) MONTH
LAST_DAY(`Report Date`)
Returns the date of the last day in the month which you're aware of but clarifying it for others.
MOD(MONTH(`Report Date`), 3)
This tells us how many months to subtract to get to the prior quarter based on the Report Date's quarter. It's dividing the month number by three to get the remainder. For November it'll be 2. 11-2 = 9 (September)
- INTERVAL ... MONTH
Subtract the specific number of months calculated above. Adding 3 to that number will take us back another entire quarter or 6 to go back two more quarters.
+ INTERVAL 1 DAY
Add a day from the last day in the quarter 2 (+3 months) or 3 (+6 months) to get to the first day of the quarter 1 or 2 quarters ago.
Re: Beast mode
In a beast mode you can do something like:
CASE WHEN `value` >= 1 THEN 1 ELSE `value` END
In a Magic ETL formula tile you can use the LEAST function:
LEAST(`value`, 1)
Re: Beast mode
Min(1, `decimal value to be capped`)
Then, on your formatting, display the number as a percentage.
nmizzell
Re: Getting Data
@Jazeppi if you need a quickstart on DDX Bricks, I made a YouTube tutorial with example bricks that show how you can retrieve data using Domo's SQL API in the brick:
ellibot
Re: Flatten relational data for import?
@patrickj I'd also recommend trying out Domo's Dataset Views feature:
Definitely depends on your use case in terms of flattening tables before Domo or inside Domo. If you find your pgsql connectors are running very slowly, I'd recommend doing fewer joins in the connector itself and instead try dataset views.
ellibot
Re: Trying to Count Instances within certain date range where 1 field is the same
You should be able to do this by grouping your data by caller and date, then joining to the Domo Dimensions Calendar dataset and then using the rank and window to create a 3 day, 4 day, etc. total. It would look something like this:
You would replace my covid sample dataset with your caller dataset. In the group by tile, group by caller and date and count the number of calls. This will give you the total number of calls by each caller on each date they called.
Add the Domo Calendar dataset. I would recommend filtering this dataset to a reasonable date range as it goes back to the 1980's and also goes into the future.
Add a Join tile and put the calendar on the left side and join on the two date columns.
In the Rank & Window, create a 3 day total by choosing Sum and your call count field and then preceding 1 and following 2. Order by the date field that is from the calendar dataset and partition by your caller. Create 4 day and 5 day totals by doing the same as the 3 day, but extending your following number.
This should get you what you want.
Re: Creating a New column
@ColinHaze There are a couple different options you could use:
- You can reach out to your CSM about enabling a fiscal calendar in your instance. Here is a knowledge base article about how they work:
- You can upload a file to Domo with a row for every date and its corresponding month. Then you can join your data to that mapping table on date in MagicETL
