7 Replies Latest reply on Jun 20, 2018 10:16 AM by Deepak Rai

# Calculate difference between the measure in max year and the year before

The goal is to calculate the difference between the measure in max year and the year before.

I got part of the solution here...but unsure how to combine it a way to work.

{FIXED [School Code]: IF MAX([Year]) THEN SUM([School Grade (Numerical)]) END}

-

{FIXED [School Code]: IF {MAX([Year])}-1 THEN SUM([School Grade (Numerical)]) END}  • ###### 1. Re: Calculate difference between the measure in max year and the year before

Try:

{FIXED School Code, Year:SUM(if {MAX(Year)}=Year Then School Grade end)}-{FIXED School Code, Year:SUM(if {MAX(Year)}-1=Year Then School Grade end)}

Or This:

SUM({FIXED School Code, Year:SUM(if {MAX(Year)}=Year Then School Grade end)})-SUM({FIXED School Code, Year:SUM(if {MAX(Year)}-1=Year Then School Grade end)})

1 of 1 people found this helpful
• ###### 2. Re: Calculate difference between the measure in max year and the year before

See if this helps:

IF [Year]={FIXED [School Code]: MAX([Year])} THEN [School Grade (Numerical)] ELSE 0 END

-

IF [Year]={FIXED [School Code]: MAX([Year])}-1 THEN [School Grade (Numerical)] ELSE 0 END

• ###### 4. Re: Calculate difference between the measure in max year and the year before

Your Year is a String?

Then use

INT({MAX(year)})

• ###### 5. Re: Calculate difference between the measure in max year and the year before

Now you also Put INT in front of Year. so use

INT({MAX(year)})=INT(Year)

INT({MAX(year)})-1=INT(Year)

• ###### 6. Re: Calculate difference between the measure in max year and the year before

I changed year to INT and the 1st option return blank, and the second option returned the correct answer. I wonder why. Thank you for your help.

• ###### 7. Re: Calculate difference between the measure in max year and the year before

I Know Why, Because we needed to Subtract Aggregations, It Works That way Only.

1 of 1 people found this helpful