How to aggregate a case statement that uses a fixed function

Chris_Wolman · 2023-01-31T16:10:49+00:00

There was an error rendering this rich post.

Hello,

I was hoping somebody could help me understand how to sum the following case statement.

case when `Date` = max(max(`Date`)) fixed () then `On Hand by Day - DO NOT SUM` else 0 end

I need the calculation to be dynamic and reference the date selected on the card. All attempts to aggregate the case statement fail and the analyzer does not offer any aggregation options.

Thanks in advance.

Find more posts tagged with

Analyzer

Beast Mode

Formulas

Accepted answers

All comments

MichelleH

@Chris_Wolman You'll likely have more luck with this if you identify the max date in a dataflow instead of using a fixed function since you're trying to aggregate based on the result of another aggregation. If you create a dataset field called `Max Date` in your dataflow, then the beast mode would look like this:

sum(case when `Date` = `Max Date` then `On Hand by Day - DO NOT SUM` else 0 end)

Chris_Wolman

@MichelleH how do I identify the max date selected on the card in the dataflow? This calculation needs to reflect the max date of the selected date on the card and calculate dynamically. The user may want to see the past 30 days or maybe the previous 3 months or any other range? I need to be able to select the on hand value as of the max date selected. Ultimately, I want to calculate a percentage using this value but I cannot get the first value correct.

Thank you.

Kpbp

Did you figure out how to do this?

Chris_Wolman

@Kpbp we ended up utilizing variables to reference the user selected periods.