Best Of

Re: Issue with using calculated field in "Values" section with Pivot Table

Are you sure this isn't caused by the data you are using and the fields that are presented in rows and columns?

ArborRose

1

Re: Input DataSet - Load New data

I can't see why only some of the tiles are displaying the error from what you've said and shown, but I can say that upsert DataSets don't support data version filtering.

Most DataSets (Replace and Append DataSets, excluding upsert) are loaded into Magic ETL in the form of a list of the originally uploaded files (usually CSV though sometimes other formats). Version filtering works by selecting a subset of those files to load, rather than loading all of them.

However, upsert DataSets are exposed to Magic ETL in the same way as DataSet Views. That is, the DataSet is exported from a database in its entirety. This exported table does not contain information to determine which rows belonged to which uploads. In the case of Views it's not clear how it ever could (since every row could be the product of any number of upstream tables), while in the case of upserts all we can say is that it does not. Currently.

I hope to see this limitation relaxed in the future. If you want to dig into the reason for the missing error messages on the other 8 of your inputs, feel free to DM me.

rco

1

Re: Need help writing a Beastmode

The formula as Grant has typed it looks correct. Assuming your typing exactly matches the condition logic.

Using a fake dataset:

hospital_name	current_balance	financial_class	aging_group
Central Hospital	15000	Medicare	0-30 days
Central Hospital	22000	Medicare	31-60 days
Westside Clinic	5000	Medicaid	61-90 days
Eastside General	30000	Private	91-120 days
Eastside General	10000	Medicare	121-180 days
Westside Clinic	7000	Private	181-270 days
Central Hospital	4000	Medicaid	271-365 days
Central Hospital	8000	Private	366-730 days
Eastside General	1500	Medicare	731+ days
Westside Clinic	32000	Medicaid	0-30 days
Eastside General	24000	Private	31-60 days
Central Hospital	2000	Medicare	61-90 days
Westside Clinic	4500	Private	91-120 days
Eastside General	21000	Medicaid	121-180 days
Central Hospital	17000	Medicare	181-270 days
Eastside General	14000	Medicaid	271-365 days
Westside Clinic	11000	Private	366-730 days
Central Hospital	27000	Medicare	731+ days

If I throw this on a pivot table and try to guess at what you have on yours.

and also throw the test calculation at the end to see if it calculates correctly…it is aggregating the proper values that I show highlighted in yellow.

ArborRose

1

Re: Need help writing a Beastmode

Try putting your aggregation outside of your case statement

SUM(CASE WHEN `Age group` IN ('121-180 Days','181-270 Days','271-365 Days','366-730 Days','731 Days+') THEN `CurrentBalance` END)

GrantSmith

2

Re: Does the PDP automation use credits?

Setting up the PDP right on the dataset will have no credit implications. PDP using the toolkit will as it requires a configuration dataset which will cost a credit every time it runs.

The toolkit is great if you have many datasets with many policies.

Directly in the dataset is great if you only have a few datasets that need PDP with simple policies on each.

Here is more information on how to use PDP Automation in Toolkit: https://domo-support.domo.com/s/article/4415800746391?language=en_US#access_toolkit

ColemenWilson

1

Re: Why is my data not being refreshed?

On your data flow, check the schedule to make sure it is scheduled. It may be set to manual, which requires manually triggering. If the log shows the data flow ran recently but you still don't have data, you probably need to verify the data source has data. Or that the process isn't filtering out your data or otherwise preventing data movement.

You don't mention how your process is running. It may require reauthorization.

ArborRose

2

Re: Error with String to Date

In that error message, the slashes are not part of the problematic value. They are placed around the value by the error message, the way quotes usually would be. They are very confusing and I am going to replace them with quotes.

My guess is the date format you want is '%c/%e/%y', like so:

STR_TO_DATE(dateField, '%c/%e/%y')

When you can't guarantee that your months and days are zero-padded (e.g. 01/01/24, never 1/1/24), you should use %c for months and %e for days. These are forgiving, and will parse the value whether it is zero-padded or not. Their padded alternatives, %m and %d, are not forgiving. They will fail if the value is not padded. This makes them useful for parsing un-delimited formats like 010124 ('%m%d%y'). If you have a delimited format and there is any doubt at all about zero-padding, use %c and %e.

rco

1

Re: Why is my data not being refreshed?

You could…

Test the API Outside of Domo
.
Run the API using something like Postman or cURL to make sure its givng back data. If the API outside of Domo also shows no data, it’s an issue with the API source itself.

Review API Parameters and Filters
Check the API parameters, such as date ranges, filters, or other query settings. If there’s a parameter set to a fixed date range or an end date, it may be excluding recent data.

Check for Rate Limits or Quotas
Some APIs have rate limits or usage quotas that, when exceeded, can return empty or partial data.

Check the API's Error Messages or Status
Sometimes the API may run but return an error or warning message

ArborRose

2

Re: MTD, MTD Last Year Beastmodes do not work properly with Date filter

There are a couple things going on

1. I'd recommend that Month and Year be variables, it sounds like they are filter cards built off the same dataset as your MTD/Last Year MTD card

2. Assuming that they are filter cards, then when you choose a Month and Year this filters your data to a single month and year which would explain why the Last Year MTD beast mode isn't returning the correct results

3. Your MTD beast mode will only calculate a non-zero value for the Current Month and Year

4. If Month and Year are already variables, that's great then you just need to add another beast mode that you will place in your filters:

CASE 
WHEN <month variable name> = month(<your date field>) 
AND (<year variable name> = year(<your date field>) OR year(<your date field>) = (<year variable name>-1)) 
THEN 'Include' 
ELSE 'Exclude' 
END

then filter the card on 'Include', this will give you the selected month and year, and also the same month for the prior year

Finally, you have to update your MTD and MTD Last Year beast modes:

— MTD
SUM(CASE

WHEN <month variable name> = MONTH(<your date field>) AND <year variable name> = YEAR(<your date field>) 
THEN Amount * -1


END)




— MTD Last Year 

SUM(CASE

WHEN <month variable name> = MONTH(<your date field>) AND <year variable name>-1 = YEAR(<your date field>) 
THEN Amount * -1


END)

ggenovese

1

Re: Google Sheets connector is available again

@HowDoIDomo - yes, you should select all the options. The connector is read only, so it won't write anything back or change anything in the Google Sheets workbook.

Tasleema

1