How to aggregate a case statement that uses a fixed function

Chris_Wolman
Chris_Wolman Member
edited January 2023 in Beast Mode

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.

Tagged:

Answers

  • MichelleH
    MichelleH Coach

    @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
    Chris_Wolman Member

    @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.

Categories