Domo Filters

carthur

I am trying to achieve filtering for Current year MTD sales per day.

I have written this simple beast mode filter. However, it eliminates totally a lot of data for some locations.

It keeps only 3 locations and I know there are data for those dates from all locations.

But when I remove the filter all the locations come through.

I checked a little deeper and without my filter when all the locations are present, if I use the DOMO built in date filters for MTD, I have no data either. Which means it may not be my built filter.

When I checked the raw data, there are definitely data that falls between those dates.

What could be going on here? What is missing.

Please help.

GrantSmith

If we use today as an example:

LAST_DAY(CURRENT_DATE()) returns 2023-10-31

LAST_DAY('2023-10-10') returns 2023-10-31

Comparing the two dates then shows that it's for the same month. It's shorter than having to compare YEAR = YEAR and MONTH = MONTH.

This wouldn't work for previous years because it's looking at CURRENT_DATE for comparison as referenced in your example however you can utilize this to see if two dates are within the same month if the return values from LAST_DAY are the same.

GrantSmith

Are there other date fields in your dataset you're attempting to utilize?

Regarding your beast mode the DAY(`Date`) >= 1 clause is redundant and will always be true. A simplified version of your beast mode would be something like:

CASE WHEN LAST_DAY(`ORDER_DATE`) = LAST_DAY(CURRENT_DATE()) THEN 'YES' ELSE 'NO' END

LAST_DAY returns the last day of the month for any date within the month.

As for your filtering it should be correct. Your data likely doesn't exist for any of the locations except those three you have listed. I'd double check your dataset and make sure you have the same filters applying, are you applying the ORDER_TYPE filter when examining your raw dataset?

carthur

@GrantSmith Thanks for the reply.

There are no other date fields and yes I was applying the order type filters on the raw data.

GrantSmith

My next step would be to create a new data table card group on the location and select the MAX of your date field to see what dates are coming across for your data. Also you can apply or ORDER_TYPE filter to it as well or include it in your table. This will help highlight if there's any missing days or when the latest order took place for each location.

carthur

@GrantSmith Could you please explain the beast mode using the last-day concept on how that will filter for MTD and potentially for previous years please MTD

GrantSmith

If we use today as an example:

LAST_DAY(CURRENT_DATE()) returns 2023-10-31

LAST_DAY('2023-10-10') returns 2023-10-31

Comparing the two dates then shows that it's for the same month. It's shorter than having to compare YEAR = YEAR and MONTH = MONTH.

This wouldn't work for previous years because it's looking at CURRENT_DATE for comparison as referenced in your example however you can utilize this to see if two dates are within the same month if the return values from LAST_DAY are the same.

carthur

@GrantSmith amazing. thanks for the explanation. would you say the below code will be the best for previous year mtd or there's simpler way of writing it?