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

# Percent difference from previous year

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

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

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