1 Reply Latest reply on May 7, 2018 12:29 PM by Joe Oppelt

    I need the last calculation result by customer

    Victor Namnum

      I'm trying to create a worksheet that only shows the latest value from the "Expected Year End Performance" formula that I created. All this formula is based on is taking latest month of this year and subtracting the same month from last year, and then add that to how the customer ended last year. This gives the estimated year-end performance for this year.

       

      I have a calculation called "Expected Year End performance" that's based on the below calculations:

      Expected Year End Performance:

      [Prior Year End Performance] + [YoY Change]

       

      Prior Year End Performance:

      LOOKUP(([Performance]),-1 * ATTR(MONTH([Date])))

       

      YoY Change:

      (ZN(([Performance])) - LOOKUP(ZN(([Performance])), -12))

       

      Performance:

      (SUM([Numerator])/SUM([Denominator]))

       

      When I drag "Customer" to Rows and "Date" expanded to YEAR MONTH to Columns, I get the below visual:

        

      However, I want to only populate the latest available value, and if no value is present,like B & F, then don't show the customer. See below what I mean:

       

      Suggestions?

        • 1. Re: I need the last calculation result by customer
          Joe Oppelt

          You can use table calcs as filters to control that.

           

          LAST() = 0 will get you the last value in a string of table-calc computed values.

           

          So:

           

          IF last() = 0 then [Expected calc] END

           

          It might take some playing around to get it all to work the way you need, but the components are there to do what you want.

           

          Also you can put another table calc as a filter to eliminate rows with no values.  (Or to display rows that have a value.)

           

          If you're just looking for ideas to get you in the right direction, there it is.

           

          But if you need help with this, upload a sample workbook and I'll work with you on it.