1 Reply Latest reply on Jul 6, 2018 10:20 AM by Yuriy Fal

    Aggregate: Aggregate by 'last' value

    banalyst
    banalyst Jun 28, 2018 8:35 AM

      Hi,

       

      I have the following data:

       

      Financial Statement AccountDateAmount
      A1-1-20185
      A1-2-201810
      B1-1-201820
      B1-2-201810

       

      I am creating a pivot, with Date (hierarchy) as columns and Financial Statement Account as Rows.

       

      When 'zooming out' from date to Quarter or Year for certain rows I would like to take the value of the last date in that quarter, So for example, in case of data above. I would like to take SUM for A and Last value for B

       

      Year2018
      QuarterQ1
      Financial Statement Account
      A15
      B10

       

      How can this be done?

       

      Thanks in advance.

        • 1. Re: Aggregate: Aggregate by 'last' value
          Yuriy Fal
          Yuriy Fal Ambassador Jul 6, 2018 10:20 AM (in response to banalyst)

          Hi bananalyst,

           

          Obviously, for the SUM for A, a simple SUM( [Amount] ) would work.

           

          As for the Last Value for B, a LOD expression would help, like this:

          IF       [Date] = { EXCLUDE [Date] : MAX( [Date] ) }

          THEN [Amount]

          END

          The above should be brought to a view

          as an Aggregation ( MIN / MAX / AVG).

           

          Yours,

          Yuri