Best Of

Create a beastmode. Replace 'FIELD' with the column that contains the values you're looking at.

CASE

WHEN 'FIELD' IN ('Level 02','Level 03','Level 04','Level 05','Level 06','Level 07') THEN 'Level 02 - Level 07'

WHEN 'FIELD' IN ('Level 08','Level 09','Level 10','Level 11') THEN 'Level 08 - Level 11'

WHEN 'FIELD' IN ('Level 12','Level 13','Level 14','Level 15','Level 16') THEN 'Level 12 - Level 16'

END

You should restructure your data so that it's stacked so you have one row per observation (sales or service)

Unit ID | Date | Date Type

1 | 1-Jan | Sales

2 | 2-Jan | Sales

2 | 2-Feb | Service

Then use some beast modes to count your sales and service units:

COUNT(CASE WHEN `Date Type` = 'Sales' THEN `Unit ID` ID END)

COUNT(CASE WHEN `Date Type` = 'Service' THEN `Unit ID` ID END)

COUNT(CASE WHEN `Date Type` = 'Service' THEN `Unit ID` ID END)
/

COUNT(CASE WHEN `Date Type` = 'Sales' THEN `Unit ID` ID END)

This way everything is on the same date field.

Is there any hope of Domo implementing the SQL FORMAT() AS function?

This kind of workaround should not be necessary.

I might be wrong, so right now your Dataset would be pivoted, so in a single Row per project you have up to 9 possible Go-Live Dates (with Initial and Actual) for each. Something like:

There are 2 approaches I can think of for this:

Beast Mode approach:

One that will return the number of times it was completed within the given window, which would look something like this but with 6 more lines for the other possible dates:

CASE WHEN YEAR(Actual Date1)=2023 AND Actual Date1 <= DATE_ADD(Initial Date1,INTERVAL 25 DAY) THEN 1 ELSE 0 END
+ CASE WHEN YEAR(`Actual Date2`)=2023 AND `Actual Date2` <= DATE_ADD(`Initial Date2`,INTERVAL 25 DAY) THEN 1 ELSE 0 END
+ CASE WHEN YEAR(`Actual Date3`)=2023 AND `Actual Date3` <= DATE_ADD(`Initial Date3`,INTERVAL 25 DAY) THEN 1 ELSE 0 END

Then a best mode to tell you the number of the dates that fall in the period, regardless of whether or not they were completed in the 25 days period:

CASE WHEN YEAR(Actual Date1)=2023 THEN 1 ELSE 0 END

+ CASE WHEN YEAR(Actual Date2)=2023 THEN 1 ELSE 0 END

+ CASE WHEN YEAR(Actual Date3)=2023 THEN 1 ELSE 0 END

And in your graphs you can do a SUM of these fields to get the total you'd expect. You can make this more versatile by having the Year and the Interval be Variables rather than hardcoded, so you don't have to redo if someone later ask, what about things that we achieve in a 7 day window rather than 25, or what if I want to know for 2022 instead of 2023. This with the table explained above will yield the following results:

ETL approach to unpivot (overkill for this request IMHO)

Then your ETL would look something like this (I know there might be more efficient ways out there, but this should do the trick):

The Make Date Pairsjust concatenates the actual and initial date for each of your possible options.

In the Select Columns, we choose only the key project information we want to keep available for charts plus the Pairs we created earlier. In the Dynamic Unpivot you select all the columns except the pairs as values that will not be pivoted.

Then we split the columns again with the Split Columns to get separate value and we force them to be treated as Dates by setting up their type with Alter Columns

Optional: This approach will create one row for each date, so 9 rows per Project. You can apply a filter rows first to only keep the dates for which you have value pairs.

You'd build your card from this new dataset instead, and you'll only compare the number of times Actual Date is less than Initial Date.

Hope this helps,

Marcel Lüthi