2 Replies Latest reply on Nov 14, 2012 7:38 AM by . Matthew

    Unexpected result - what am I missing?

    . Matthew

      Using Superstore as an example, I am comparing sales in one month to the previous.  With a straight forward table calc in an IF statement, I am attempting to group the customers into those who've shown growth (the winners); those who've stayed the same (static); and those who've gone backwards (the losers).


      There are 2 sheets in the attached workbook showing the same thing - colored squares which is my preferred output, but I've also included the numbers in the 2nd sheet so you can see what is really going on.  I'm switching between the groups using a quick filter on the viz.


      The winners and the losers groups each look to be good, but the static group defies imagination as instead of picking up the zeros it is collecting what appear to be random winners and losers.  The calc seems to be failing as 'static' is returning a NULL when there's an absence of sales in any one month, but I thought that using ZN() would overcome this issue.


      Can anyone shed some light on what I am missing here and what I might do to fix it?  Thanks in advance.

        • 1. Re: Unexpected result - what am I missing?
          Mark Bingham

          I believe the your calculation is working correctly - but it appears that the static categories are beign compared against a blank field in either the Sept column or the Oct column.  I may be missing something, but I believe this is likely why there are so many 'static' results.  Those blank fields cause it to fall into the final 'else' logic of your calc.

          • 2. Re: Unexpected result - what am I missing?
            . Matthew



            Thanks for your response.  You are correct in that the problem is caused where there is blank data for a customer in either September or October.  What I don't understand is why the ZN function doesn't stop this from happening.


            The table calc (using ZN) works in so far as the calculation to show the difference between the two months calculates correctly and this can be seen in the 2nd sheet - when it comes to the 'difference' every customer has an entry.


            It would appear that the IF calc is running ahead of the table calc which it is why it fails.  What I would like to be able to find out is how to change this behaviour so it will calculate in the way I am after OR if someone could suggest another method entirely.  I am not wedded to the route, it's the destination that counts.