How to aggregate a case statement that uses a fixed function

Chris_Wolman · January 2023

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.

MichelleH · January 2023

@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 · January 2023

@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 · April 26

Did you figure out how to do this?

Chris_Wolman · May 6

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

How to aggregate a case statement that uses a fixed function

Answers

Categories