6 Replies Latest reply on Jul 19, 2016 7:09 AM by Ray Yu

    How to lock a calculation result

    Ray Yu

      Hi,

      I am trying to compare the monthly sales of this year with the sales of December last year. The data fields I have are year, month, sales.

      So for example, I have sales 100 for Jan 2016, 200 for Feb 2016 and 300 for Mar 2016. The sales for Dec 2015 was 50. I want a graph that would show the difference 50, 150 and 250.

      I was trying to use calculation: if [Year]=[PY] and [Month]="12" then [Sales] END to get the sales of last year period 12. But I can't include this number in my difference calculation.

      So I would like some helps on this.

       

      Thanks,

      Ray

        • 1. Re: How to lock a calculation result
          Naveen Agarwal

          Hi Ray

          Looks like you want to use the Dec 2015 sales as a fixed reference value, which needs to be subtracted from sales of each of the following months.

           

          As long as the reference month (in this case Dec 2015) is the First month/year in your dataset, the approach I took will work. You can get a graph like this:

           

          I created a Table Calculation:

           

          Reference Sales = lookup(sum([Sales]),first())

           

          Then I subtracted this number from each of the other values

           

          Change in Sales = sum([Sales])-[Reference Sales]

           

          You can now have a graph of Change in Sales by Month/Year.

           

          Does this work?

          • 2. Re: How to lock a calculation result
            Ray Yu

            Hi Naveen,

            Thanks for your help. Unfortunately Dec 2015 is not the first month in the data. I have 2 year, 24 months' data. So first() won't apply here.

             

            Thanks,

            Ray

            • 3. Re: How to lock a calculation result
              Naveen Agarwal

              Do you want to compare sales to Dec 2015 moving forward only (i.e. in 2016) or do you also want to do this backwards? If you only want to compare 2016 sales, then you could apply a filter to select only Dec 2015 onward. Then you could use this technique.

               

              If not, then you need to setup a calculation with a constant offset equal to Dec 2015 sales and apply to all data.

              • 4. Re: How to lock a calculation result
                Ray Yu

                Hi Naveen,

                Thanks for your advice. Although it's not perfect, I will apply a filter to only select Dec 2015 onward.

                 

                Thanks,

                Ray

                • 5. Re: How to lock a calculation result
                  swaroop.gantela

                  Ray, Naveen,

                  Apologies for jumping in.

                  I may not have caught all the details,

                  but I was wondering if you could use a Level of Detail calculation to

                  get the value of the Dec2015 sales and fix it for all values, and then

                  you could make the subtraction for every month:

                   

                  {FIXED  : SUM(

                  IF [Order Date Parameter]=YEAR([Order Date])

                  AND MONTH([Order Date])=12

                  THEN (Sales)

                  END

                  )}

                   

                  You could then subtract off this amount from the SUM(Sales) of other months:

                  SUM(Sales)-SUM([Dec2012Sales])

                  1 of 1 people found this helpful
                  • 6. Re: How to lock a calculation result
                    Ray Yu

                    Hi Swaroop,

                    Thanks for your help! This is exactly what I was looking for!

                    A good learning of level of details calc use

                     

                    Thanks,

                    Ray