Force Pie Chart to show data for only most recent day of date range

jimsteph
jimsteph Contributor
in Cards, Dashboards, Stories

I have a dataset that has the following columns: Date, Store, Plan, Total. My users want a pie chart that shows the total for each plan type on the last day of whatever date range the filters set, i.e.: if today is the 24th and the filter is set to month to date, I want only the values for the 24th. Unfortunately, no matter what I do I end up with the total across all dates in the date range. I've tried the date in the filter, but it only lets me choose a specific date, not the max date in the range. I tried to make a beast mode that set a flag to 1 if the date was equal to the max of the date range (CASE WHEN `Date` = MAX(`Date`) THEN 1 ELSE 0 END), but when I put the data into a table the flag was 1 for all dates in the range, so I'm obviously doing something wrong there. Can anyone give me a pointer in the right direction?

Tagged:

Answers

  • trafalger
    trafalger Coach
    edited August 2023

    So you have the right idea with your case statement, but that statement executes on a row by row basis, so it's only comparing the date of the row to the MAX(Date) of each row.

    Add a MaxDate in the dataflow and then do that comparison there.

  • jimsteph
    jimsteph Contributor

    If I'm understanding, I'm not sure how this would work: my users might set the dashboard's date filter to month to date, or to previous month, or to previous year, or any of a hundred other ranges, and I can't account for each of those in the flow itself. Is there no way in a beast mode to get the max value across all rows allowed by the date filter, and then use that value?

  • GrantSmith
    GrantSmith Coach

    You can try and use a beast mode with a fixed function and filter on that beast mode:

    CASE WHEN `dt` = MAX(MAX(`dt`)) FIXED () THEN 'Latest' ELSE 'Not' END

    This example uses a dt date field but you can then put this into the filter and filter for "Latest"

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ST_-Superman-_
    ST_-Superman-_ Coach

    I agree with @GrantSmith here. By using a fixed function, you should be able to check each date against all other dates coming through the filters. I would be curious to hear if this didn't work.


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

Categories