1 Reply Latest reply on Nov 4, 2016 1:20 PM by Stoyko Kostov

    Aggregation error when trying to compare a value to Max value?

    Bob Liu

      So I'm trying sum up counts from the latest month in each evaluation bucket because the data is commutative.

      Assuming 201512 is the highest month, this formula works great:

       

      PROCESS_CYMD (#) is numeric valuable with year and month in YYYYMM and COUNT valuable has 1 or 0 for each row.

       

      IF [PROCESS_CYMD (#)] = 201512 THEN COUNT ELSE 0 END - I get 78, which is correct

       

      However, I don't want to hard-code the maximum value month.

       

      I tried the following

       

      IF [PROCESS_CYMD (#)] = MAX([PROCESS_CYMD (#)]) THEN COUNT ELSE 0 END - Mix Aggregation Error

      IF ATTR([PROCESS_CYMD (#)] = MAX([PROCESS_CYMD (#)]) THEN ATTR(COUNT) ELSE 0 END - I get zero value

      IF ATTR([PROCESS_CYMD (#)] = MAX([PROCESS_CYMD (#)]) THEN Sum(COUNT) ELSE 0 END -  I get zero value again

       

      I'm at a loss on how to proceed with this. Please advise.

       

      Thanks,

        • 1. Re: Aggregation error when trying to compare a value to Max value?
          Stoyko Kostov

          Hi Bob - try LOD instead of aggregation, like this:

           

          IIF([PROCESS_CYMD (#)] = {FIXED : MAX([PROCESS_CYMD (#])}, [COUNT], 0)

           

          Let me know if this helps. You may have to adjust the dimensions in the FIXED part of your LOD depending on the context in your worksheet you are trying to use this in.

           

          If you are still confused, please provide a sample packaged workbook and describe your desired outcome - I'd be glad to help further.