Date Reference Exclusion/Inclusion

Brian_WPI
Brian_WPI Member
in Beast Mode

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.

Bar Chart.png
Table Card.png

Tagged:

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • Ashleigh
    Ashleigh Coach

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

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • Brian_WPI
    Brian_WPI Member

    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
    DavidChurchman Coach

    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

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Categories