2 Replies Latest reply on Mar 2, 2016 9:09 PM by Steve Gesuale

    calculating % difference between calculated fields

    Steve Gesuale



      Looking for help with a pesky calculated field.


      Data is at daily level with one measure sales and another sales from the same date for the previous year (Sales YAGO).


      These are used in conjunction with a date parameter to calculate the year-to-date sales (YTCD - Sales) and year-to-date for the previous year (YTCD YAGO - Sales).


      I’m trying to then calculate the % difference between year-to-date sales and the previous year's year-to-date sales.


      Using this calc which seems correct but can’t figure out why it’s returning “incorrect” values.


      sum([YTCD - Sales]-[YTCD YAGO - Sales]) / sum([YTCD YAGO - Sales])


      For example, Boston has lower sales this year than last but the calc is showing it’s 15% up.


      I suspect it's something do with using a calc on top of other calc's?


      Would love some help in sorting this out from any experts!




        • 1. Re: calculating % difference between calculated fields
          Steve Mayer

          Looks like there are NULL values for TYCD - Sales or YTCD YAGO - Sales, and that is causing some of the sales to be omitted using your current formula.


          Both of these alternatives yield the correct result:


          This sums YTCD separately from YTCD YAGO, so NULL values don't change the result.

          (sum([YTCD - Sales])-sum([YTCD YAGO - Sales])) /

          sum([YTCD YAGO - Sales])


          This just handles NULL values in your current formula.

          sum(ZN([YTCD - Sales])-ZN([YTCD YAGO - Sales]))/

          sum([YTCD YAGO - Sales])


          1 of 1 people found this helpful
          • 2. Re: calculating % difference between calculated fields
            Steve Gesuale

            Hi Steve,


            Thanks for the quick and very helpful reply!


            The keepers of this data recently changed it to use blank cells instead of zeros for "closed" store days so averages would calculate properly.


            So you picked up on what I should have - appreciate the help!