1 Reply Latest reply on Oct 14, 2016 8:00 AM by Joe Oppelt

    Conditional Formatting Question

    soumya sinha

      Hello there,


      I Have a conditional formatted table that is set up in a way if the % diff goes up it's green, if it's done it's red and if it's netural it's ornage. For some reason the grean total are off. At individual level the % diff are fine but the totals are messed up. This is the formula I am using for calculating the % diff and then for the color is the one after.


      (ZN(SUM([Sum_Media Cost])) - LOOKUP(ZN(SUM([Sum_Media Cost])), -1)) / ABS(LOOKUP(ZN(SUM([Sum_Media Cost])), -1))


      IF [%D_Media Cost] >.1 THEN "Up" ELSEIF [%D_Media Cost] < -.1 THEN "Down" ELSEIF [%D_Media Cost] >= -.1 THEN "Neutral" END


      Can someone tell me what might be messing up the totals?

        • 1. Re: Conditional Formatting Question
          Joe Oppelt

          When I have an issue like this, I create calcs to grab the intermediate values.   And then I display those values in tooltips or on the text shelf to see what's actually happening under the hood.


          Is [%D_Media Cost] actually getting calculated correctly?  If you're just seeing colors, what are the actual values getting calc'd?  Display them and see.  And if the values are not actually getting calc'd correctly, what is the value of ABS(LOOKUP(ZN(SUM([Sum_Media Cost])), -1))?  And LOOKUP(ZN(SUM([Sum_Media Cost]))?


          See what actual values are getting used on a row-by-row basis.  When I do this I can usually see where my logic fails.