7 Replies Latest reply: Oct 16, 2015 10:19 AM by Kevin Bertsch RSS

    Find the maximum value in pivot table

      Hi, we chart our sales through a series of stages, 20, 40, 60, 80, and won/lost. The expression portion of the table looks like this:

      stageprogress.png

      I used the "AFTER()" function to set background colours, depending on the length of time the sale was in a particular stage. However, I have been asked to modify this to find the SINGLE stage for each customer (not shown, but they are the first dimension) where the sale has sat the longest, and set that background colour to red.For example, on the 11th row, I would subtract the 60 date from today, and since that's more than the difference between the 40 and 60 stage (6/8-4/29 << 10/14-6/8), I would just colour the 60 date red.

       

      If there's only one stage shown, I need to set that to red.

       

      Finally, if the sale has gone 'backwards' (e.g. 7th row above, where it went from 60 on May 2 to 20 on June 7), I need to set that to red.

       

      The last part (backwards) is pretty easy, but how would I find the max in say, line 16 (4/2/15,4/17/15,5/6/15) succinctly? I guess I could do it with a ton of nested IF statements, but I was wondering if there was a more elegant way to do it.

       

      Thanks for any assistance!