Can i Sum the value in a set of data based on the max date selected in the date filter

Sara321
Sara321 Member
in Charting

Hi

I have a set of data which is a snap shot of records each month. I want to Sum one column only where the date is equal to the latest date selected using the date filter in the chart. I tried the formula below but it is giving me no output.

SUM(CASE WHEN `ACV Fiscal Month`= MAX(`ACV Fiscal Month`) then `ARR Amount(USD)`else 0 end)

The Max formula works on it's own but it doesn't seem to like being compared to in the when statement..


Many Thanks in advance for any help!

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Sara321

    You can't aggregate an aggregate. You'd need to use a dataflow to pre-aggregate your data to calculate your max month. The downside to this is that it's not affected by any filtering taking place on your card.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Sara321

    You can't aggregate an aggregate. You'd need to use a dataflow to pre-aggregate your data to calculate your max month. The downside to this is that it's not affected by any filtering taking place on your card.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Sara321
    Sara321 Member

    Thanks @GrantSmith I had a feeling you would say that...

Categories