Date Reference Exclusion/Inclusion

Options
Brian_WPI
Brian_WPI Member
edited July 7 in Calculations & Variables (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 Domo Employee
    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.

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 Domo Employee
    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.

Categories