sharonsql2013 (10/31/2013)
I am trying to find out the sum of a filed named "PercentColumn" which is calculated column based on PreviousYear and Currentyear.The expression of the "PercentColumn "is :
=IIF(Fields!PreviousYear.value = 0,0,
(Fields!Currentyear.value - Fields!PreviousYear.value)
/IIF(Fields!PreviousYear.value = 0,1,Fields!PreviousYear.value))
I am taking care of "Zero" in the denominator and finding the % increase and decrease. Up until here the results are populating fine. But,My next column should have a sum of this "Percent Column". So I am using sum of the above expression but its returning an error ...
Can anyone pls advise.
Your original expression is checking for PreviousYear value twice. The second time is not required as the expression will only calculate if the PreviousYear is not 0 (by the first if).
=IIF(Fields!PreviousYear.value = 0,0,
(Fields!Currentyear.value - Fields!PreviousYear.value) / Fields!PreviousYear.value)
Now if you are writing a report then you could get the "sum" in two ways. Usually you would not want to sum the percentages or to sum the PreviousYear and CurrentYear then calculate the percentage of the whole.
Case 1 :
= sum(IIF(Fields!PreviousYear.value = 0,0,
(Fields!Currentyear.value - Fields!PreviousYear.value) / Fields!PreviousYear.value))
Case 2:
=IIF(sum(Fields!PreviousYear.value) = 0,0,
(sum(Fields!Currentyear.value) - sum(Fields!PreviousYear.value)) / sum(Fields!PreviousYear.value))
Taking as test values :
PreviousYear | CurrentYear | Percentage
0 100 0 (0%)
100 100 0 (0%)
200 100 -0.5 (-50%)
100 200 1 (100%)
Case 1 : 0 + 0 -0.5 + 1 = 0.5 (50%)
Case 2 : sum(PreviousYear) = 400, sum(CurrentYear) = 500
(500-400) / 400 = 0.25 (25%)
Case 2 shows how the total sales etc has grown correctly.
Fitz