Date Reference Exclusion/Inclusion

Brian_WPI · 2024-01-18T22:50:59+00:00

There was an error rendering this rich post.

I'm attempting to write a beast mode that checks a static date with a dynamic minimum date. If the static date is greater than the dynamic minimum date it would be classified as 'No' and vise versa.

The beast mode formula:

CASE WHEN Onboarding Date <= (MIN(MIN(Report_Date)) OVER()) THEN 'Yes' ELSE 'No' END

When I use the filter in a table card, it works correctly. However, when I try to use a bar chart the filter only has one option.

Date range and aggregation are identical in both cards.

Find more posts tagged with

Date

Filters

Beast Mode

Accepted answers

ST_Superman

@Brian_WPI

My thought here is that your table card (where the field is working correctly) is displaying more fields than the bar chart. You may want to include a partition by clause in your beast mode.

CASE WHEN Onboarding Date <= (MIN(MIN(Report_Date)) OVER(PARTITION BY `Place`)) THEN 'Yes' ELSE 'No' END

Essentially, my guess as to what is happening is that your table card is forcing the calculation to only look at a single "Place" at a time while your bar chart needs the partition to be specifically stated.

All comments

Ashleigh

@Brian_WPI Not sure if this will make a difference but check what the date range settings are. The graph shows Last 12 months by month, not sure what the table is doing but it shows Last 12 months. The bar might need to be changed to have default in the graph by or you may need to update the BM to calculate it over months.

Brian_WPI

Thanks for the tip. I've tried adjusting both card time ranges to Graph by "Month" and "None". Neither seem to affect how the filter functions.

DavidChurchman

The formula should work. I tested it on a similar dataset and was able to get both 'yes' and 'no' values in the control for a by-month bar chart. Two things to try:

Maybe try adding that minimum report date as a tooltip to the bar chart?
Double check you don't have a card-level filter applied

ST_Superman

@Brian_WPI

My thought here is that your table card (where the field is working correctly) is displaying more fields than the bar chart. You may want to include a partition by clause in your beast mode.

CASE WHEN Onboarding Date <= (MIN(MIN(Report_Date)) OVER(PARTITION BY `Place`)) THEN 'Yes' ELSE 'No' END