Best Of
Re: Case when and formula with date
On your top condition you are returning `Total referrals `=0. If you want it to be 0 in this condition then just return 0
case
when `Parent OR AgencyName` = 'Aspire Home Healthcare' and `Date` >= '10/23/2024' then 0
ELSE `Total referrals`
end
Re: Need help writing a Beastmode
In beast mode, CASE statements can’t perform aggregate functions like SUM within the conditional logic. Use the CASE statement to identify the rows that meet your criteria, and then apply the SUM function on that logic at the card level.
I'm not sure what you are trying to do with your conditional logic. You are saying if the age group is A, B, or C then do something. Normally you would have a case WHEN for each or some of those age groups. From what I see, you are asking for current balance on any age group over 120 days.
CASE
WHEN `Days Past Due` > 120 THEN `CurrentBalance`
ELSE 0
END
Create the formula, and then aggregate (SUM) on the card.
Re: Pivot Table Expansion
This is not currently possible. You could submit this as an idea in the
Re: How to format "or" clause in Query?
Domo's Javascript may not support .or(). Using the following code, you should be able to get the include to return true if either condition is met….creating an or condition.
query
.select([...]) // <- specify select columns here
.where(row => row.errorMessage.includes('Some text') || row.errorMessage.includes('Some other text'))
.limit(1000);
Re: Error with String to Date
@ggenovese never heard of FUZZY_PARSE_DATE(), super helpful function!!!
Re: Error with String to Date
there's a FUZZY_PARSE_DATE() function in Magic ETL, I tested with the string '/3/31/24/' and it converted it to date without an error
Re: Keep data in column as constant regardless of date
In an ETL, isolate your quota data and filter so you only have 1 row per rep, per quota. Then join that back to all data on Rep = Rep. If you provide sample data with Rep, Quota, and sales amount fields I can build it in an ETL and show you.
Re: Count the number of upper case letters in a string
I think you have to do this in an ETL, because REGEXP_REPLACE() doesn't work for me inside of a card.
LENGTH(REGEXP_REPLACE(`sample`,'[^A-Z]',''))
Re: Count the number of upper case letters in a string
LENGTH(Uppercase) - LENGTH(REGEXP_REPLACE(Uppercase, '[A-Z]', ''))
Re: How do I fix a string error with a date?
@kim_barragan0126 Based on your screenshot, it looks like your source data is from Excel. Likely what's happening is that you have a typo in one of your date columns. Since you only have 5 date columns, I'd suggest opening the filters of each date column to look for invalid date values. If a value is invalid, Excel cannot group it under a particular month/year with expandable "+" signs, which makes it easy to filter to bad values and correct them in the sheet before uploading.
Alternatively, you can try converting your datatypes in the ETL using the TRY_CAST function in a formula tile instead of the Alter columns. When using TRY_CAST, if it encounters a value that it cannot convert to a different datatype then the output for that row will be NULL instead of causing the dataflow to fail. Your formulas should look like this:
TRY_CAST(`Best Few Date` as DATE())
