Custom Month Filter for building cards

DANIH · 2021-09-24T01:23:19+00:00

There was an error rendering this rich post.

Hello!

I am currently building simple gauge cards that show sales for today, week, and month vs our goal for each of those. (see screenshot)

Is there a way for the 'This Month' card to have a custom date range each month and have that automated so I don't have to change all the cards each month to a new date range? I work in retail so each month is different and it depends on start of week not starting with the first of the month. For example this month (September) for us is from August 29th-Oct 2nd.

Hopefully there is a way without too much calculations. Thanks in advance.

Find more posts tagged with

Date

Card Builder

Accepted answers

All comments

GrantSmith

Its likely possible with a beast mode and just filter the card based on when that beast mode is the current month. The question is how do you define the current month?

DANIH

Well that's the thing I'm trying to figure out. I know what all the months are from beginning to end so how would I use that is a beat mode? Then how does that filter the data automatically?

MarkSnodgrass

You can do this if you set up a web form that has a list of when your start and end dates of each your months. Like this for example:

Next, create a MySQL dataflow that joins this web form dataset with your Shopify dataset so that you will have these start and end dates as additional columns to your main dataset. Here is an example joining the above webform to the covid dataset:

SELECT d.`date`, d.`confirmed_total`, m.`StartDate`, m.`EndDate`, m.`Display Name`
FROM `domo_covid_time_series_tracker_data` d
JOIN `test_start_end_months` m on d.`date` BETWEEN m.`StartDate` and m.`EndDate`

Finally, build your card off of this dataset and then create a beast mode called Current Month that looks like this:

(CASE WHEN `StartDate` <= CURRENT_DATE() and `EndDate` >= CURRENT_DATE() THEN 'Y'
ELSE 'N' END)

Drag this field into your filters and filter to Y. This will allow your card to dynamically move to the current month and you won't have to touch it.

GrantSmith

With Domo when you're using the date selector it determines which month you're currently in and selects days 1-28/29/30/31 (month depending). It sounds like your business definition of a month is different since you're expecting the current month to be August 29th-Oct 2nd. How do you know that August 29th is the first day of the current month and the 2nd is the last day of the current month?

MichelleH

@DANIH If this is something you will use a lot you could also reach out to you Domo account team about implementing a fiscal calendar for your instance. As long as you can provide enough information for how your company's months are structured they can give you the option to use that calendar in your cards if you check the "Use fiscal calendar" box in the Analyzer. This would work perfectly for your "Current Month/Previous Month" example. Note that the fiscal calendar only applies to card Date Selectors, and not Beast Modes/ETL.

For use cases that don't fit in the constraints of the native card date selector, we have used an approach similar to what @MarkSnodgrass suggested where we maintain a mapping table that lists every day of the year with its associated week/month/quarter/year from our company calendar. Then you can use a simple join on any date field to add the fiscal month as a column in your data.