Last value in a given time period

efleming
efleming Member
in Beast Mode

I'm trying to write a beast mode to give me the last value depending on how the time data are grouped (e.g. by week, then value on last day of each week; by month, then value on last day of each month). I believe I could do this in magic ETL but then the end user can no longer choose the time grouping they're interested in dynamically on the card.

The columns are: [member_count, date] with example values like: [100, 2024-01-15]

In the current format of the data there might not be values for every single day in a timeframe (e.g., 2024-01-16 might be missing). I could probably fill in every date if it would help.

I tried doing something like: CASE WHEN date = MAX(date) THEN member_count ELSE 0 END

But then I wasn't able to aggregate that beast mode - I think MAX(date) may not be the right choice. Thanks for any help!

Answers

  • brycec
    brycec Coach

    I've done this with a DataSet that was grouped by month, so that it could be changed to by quarter or year and still be getting only the most recent value of those time groupings and not duplicate. I imagine the same concept can be used by day too. I achieved that with the following formula:
    (CASE
    WHEN Month = MAX(MAX(Date) FIXED (REMOVE Month, Relationship Status, Repurchase Type)) THEN 'Yes'
    ELSE 'No'
    END)

    The Date column is already grouped by month in the DataFlow. The Month column is just an exact duplicate of the Date column, but it has to be added in the DataFlow, not on the card. Then, just add the Beast Mode as a filter on the card and select Yes. You'll want to have Month (or in your case Day) in the REMOVE part of the FIXED function. The other columns are any other column that you want to have as a series in the chart.

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

Categories