Domo Filters

carthur
carthur Member
in Beast Mode

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.

image.png
image.png
image.png

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

image.png

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.

image.png

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.

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • carthur
    carthur Member

    @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
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • carthur
    carthur Member
    edited October 2023

    @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
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • carthur
    carthur Member

    @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?

    image.png

Categories