2 Replies Latest reply on Jun 13, 2016 11:41 AM by Karthik Kasu

    Percent difference from previous year

    Karthik Kasu

      I have a requirement in which a user wishes to see the percentage change of a value from previous year.

      The user has to select the year and table calculation has to be done and compare the % change and show the difference.

       

      I'm using the calculated field with a parameter to show the difference.
      This is my calculated field.

       

      SUM((IF YEAR([ACQUISITION_DATE])=YEAR([Select Month]) AND MONTH([ACQUISITION_DATE])=MONTH([Select Month])

      THEN [Penetration] END))

      -

      SUM((IF YEAR([ACQUISITION_DATE])=YEAR([Select Month]) AND MONTH([ACQUISITION_DATE])=MONTH([Select Month])-1

      THEN [Penetration] END))

       

      But here there is a error. The penetration field is a calculated field that derives from sum(acqs)/sum(sales). The above calculated field throws an error saying that cant compare aggregate and non-aggregate functions in IF statement.

       

      How can I solve this issue?

      Is created a table calculation. Is there any way that i can Insert my date parameter([Select Month])) in my table calculation? So that that table calculation calculates based on parameter my user selects?

       

      Thank You

        • 1. Re: Percent difference from previous year
          Christina Gremore

          Hello Karthik,

           

          The issue is that the YEAR and MONTH functions output an integer. So the YEAR and MONTH functions are "non-aggregated", so they're causing this error, because the [Penetration] field is aggregated. Wrapping the entire IF/THEN statement in a SUM won't work, because the inconsistency is within the IF/THEN statement - the error is coming from inside the house, as it were

           

          One fix would be to throw a MIN or a MAX around those date fields, which 'aggregates' them. See example below:

           

          (IF MIN(YEAR([Order Date]))=MIN(YEAR([Select Month])) AND MIN(MONTH([Order Date]))=MIN(MONTH([Select Month]))

          THEN [Profit Ratio]

          END)

          -

          (IF MIN(YEAR([Order Date]))=MIN(YEAR([Select Month])) AND MIN(MONTH([Order Date]))=MIN(MONTH([Select Month])-1)

          THEN [Profit Ratio]

          END)

           

          If this helps resolve your issue, please mark it as the correct answer. Thanks!

          1 of 1 people found this helpful
          • 2. Re: Percent difference from previous year
            Karthik Kasu

            Christina,

            Thank You for the solution.

            I need sum for getting the requirement.

            I got the solution.

             

            Please see my solution, this fixed my problem:

             

            (SUM((IF YEAR([Order date])=YEAR([Select Month]) AND MONTH([Order date])=MONTH([Select Month])

            THEN [Profit] END))/

            SUM((IF YEAR([Order date])=YEAR([Select Month]) AND MONTH([Order date])=MONTH([Select Month])

            THEN [SALES] END)))

            -

            (SUM((IF YEAR([Order date])=YEAR([Select Month]) AND MONTH([Order date])=MONTH([Select Month])-1

            THEN [Profit] END))/

            SUM((IF YEAR([Order date])=YEAR([Select Month]) AND MONTH([Order date])=MONTH([Select Month])-1

            THEN [SALES] END)))

             

             

            The above calculated field worked and it is going.

             

            Thank you

            regards