marcel_luthi Coach

Thanks, I guess that is the option left, not a fan of it, in part because I liked the aesthetics of this kind of card more, but mostly because for the other cards I'd need to create a separate Beast Mode for each of the categories and then when you drill down you'd always drill into the full universe of samples and not…

Sure, lets say I have the following dataset, which matches the structure of the table you first provided: The ETL will look something like: CODE (you can copy and paste this into your ETL interface): {"contentType":"domo/dataflow-actions","data":[{"name":"TEST -…

For this you'll need to join your table in on itself (left join) using the as key for the left table the Project Name + Document Number and for the right table the Project Name + Revision Left that way you'll only get entries to the new columns when the a match is found, you set this to 0 and then do a formula tile to…

You can use the STR_TO_DATE function to achieve this in conjunction with CONAT. You can find the reference for available functions here: So your formula will look something like: STR_TO_DATE(CONCAT('01/',`Month`,'/',`Year`),'%d/%m/%Y')

Can you share a screenshot of your Group By tile configuration? The ID should be the column to identify the grouping (1), whatever name you want in (2) and the column containing the values with the SUM aggregation in (3). There should also be no need to do any NULL handling for the values to be aggregated.

This sounds like a use case for Variables, I would create a variable to select the time period: Day, Month, Quarter, Year, etc. and then a second variable asking for a date in the period. This way if the date entered is 2/17/2024 then depending on the first variable (time period) you'd get: Day: only the entries for…

There are limitations on what Dashboard filters can do for you. Each dimension you add to the filters will be an ADD condition (so criteria for ColumnA and ColumnB both need to evaluate to TRUE), but any additional options you add inside of each of these are used with an OR logic as you've described. There could be…

REGEX can be tricky, specially when it comes to replacing, so you might want to try a couple of different options until you find one that works, here's one you can try: REGEXP_REPLACE(`Information`,'.*([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9_-]+).*','$1')

That is expected, when you Append 2 files, at the end you need a consolidated set of columns that will remain in the appended version, this means if they do not have the same columns, like in your case, that one of the following will happen to 1 or more of the original sources being appended: Columns will be deleted…

You can kind of simulate a merging if you use a Beast Mode calculation and concatenate, the big problem with it is that you loose all the cool features about number formatting, so you'll have to manually set those up. Here's an example of something I did not long ago (this inside a pivot table): There I have a single…

@art_in_sky AVG works based on the number of non null entries, so if you null out your zeros that will solve for it. A beast mode like: CASE WHEN `Column` = 0 THEN NULL ELSE `Column` END Which then you'll select to aggregate using AVG should do the trick or removing the 0s from your AVG calculation (or you can aggregate at…

Just to be clear, what I posted is not the full Beast Mode, just what I suggested to replace the BETWEEN condition inside of each of the WHEN statements and not meant to be entered on it's own. I'd guess you're using the Multi-Value Columns Chart Type, if so what does your source dataset looks like, for this to work you'll…

I can think of 2 ways to approach this: ETL and using a window function so you can use LEAD() and have the data pre-aggregated to the Month Level. Beast Mode since you want to the Sum of February in January you'll need to leverage the FIXED function and write a WHEN statement for each possible month (or at least…

I try not to use BETWEEN as I seem to never get it right, try replacing BETWEEN with two comparisons something like: WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 4 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) Replaced by WEEK_STARTING >= DATE_SUB(DATE_SUB(CURRENT_DATE(),…

Do the cards still have the Show Subtotal option selected or has this been deselected by some sort of evil magic? Also when you say subtotals are missing, do you mean the row not showing at all, or the value for a certain column not being totalized at the subtotal level? This could indicate whether is a Dashboard property…

The separation for Null should work, you can do it in several different ways, so your first statement would be: CASE WHEN `New_Column_Name` IS NULL THEN 'a) Blank' … And from there you cover all the other scenarios. Since case is always checked in order and stops after the first condition met is fulfilled, for example if…

You have a comma at the end of the first line, you don't separate WHEN statements with commas, this is most likely why you're getting the error with your syntax. I do agree with @GrantSmith that this could be a good use case for unpivoting, specially if a single Row might have a value of 1 for more than a single year, as…

Since CASE is always checked in order, you can simplify your beast mode to something like: CASE WHEN COALESCE(`New_Column_Name`,0) = 0 THEN 'a) Zero or Blank' WHEN `New_Column_Name` < 5 THEN 'b) Less than 5' WHEN `New_Column_Name` <= 10 THEN 'c) 5-10' WHEN `New_Column_Name` <= 20 THEN 'd) 11-20' WHEN `New_Column_Name` <=…

Perhaps I'm oversimplifying it, but you should be able to do this with just 4 tiles: a Dynamic Unpivot, a Formula, a Alter Columns and a Pivot one. As for performance I you'd need to test it out but in General this is what the ETL would look like: Input Dataset (you could have any number of Stage # columns for each…

Nice and well deserved!

I can say I was convinced he was a Coach in disguise since before I joined, hiding his real title all this time 🤣. Congratulations and thanks for all the awesome videos you create to walkthrough the different questions here.

Time to run will depend a lot on the number of records you have on your original dataset and the operations being performed, while unpivot usually is usually a heavy operation, I'd like to know why 3 tiles? Can you share a sample (with mock data or sanitized) of what the Input looks like and what the output should be?

First, what does the raw data looks like? and then if you can do a doodle or something of what your desired visual would be? The reasons I'm asking this is because it seems to me you're talking about 3 different cards which you can put together in a dashboard to show all these entries. There are also some key things you…

Is there a way you can add a row number to your original dataset? We can in theory do this in ETL but without having a real column to sort on, I'm not sure if the method I've used that worked for a small dataset will keep on working all the time. Original: Backfilled (you could rename or remove columns as needed, but I…

You could try an IN statement instead of OR, something like: CASE WHEN TRIM(UPPER((`PRODUCTSTATUS`)) IN ('R','REPEAT') THEN 'Repeat' ELSE 'New' END I'm setting it to upper case and trimming whitespaces just to cover the possibility of the data not being clean, which is what I could guess based on the output you currently…

It depends on what your raw data looks like. If the card you showed us is a Pivot Table, you can do this purely with Beast Modes. I'll assume the values you have are a COUNT of activities for this purpose. In this case a Beast Mode like the one below might do the trick (FIXED is a powerful function once you figure it out)…

You could use a text card and a beast mode, I guess it all depends on what you'd expect the text to be. I'd leverage the Domo Calendar dataset just to be sure my tile will still show data even when I might not have entries in my actual datasets I'm building the cards from (since I guess you'd only want to show the name of…

This sounds like a good option to use a Histogram (if you don't need that much granular control over the partitions). If you need custom partitions, I'd strongly advise doing a View of your dataset that already aggregates the total time at the DIV_OrderNum level and then graph based on this with a simple ETL to bucket…

I like @MichelleH approach. I guess the only question after is what should you do if you happen to have an entry on a weekend for some weird reason, should you those be pushed to the next working day or what? (Hopefully this doesn't happen but is good to prepare for it). If I'm following her correctly after the work is…

Totally agree, this is a headeache because the 1st week of each month does not have the same number of business days in it, and this clearly affects your metrics. The hardest part would be to come up with a name that everyone would agree for that standalone day, as it'll also need to be it's own standalone month as for not…