I am having trouble calculating the variance to prior year end. Here are my formulas:
Auto 2011 YE = if [Termination Date] = DATE(12/2/2011) then [LEW %] end
Auto Current Month = if [Termination Date] = DATE(7/1/2012) then [LEW %] end
Auto Vaiance = ([LEW %]-[Auto Target])*100
For the first two equations, I keep getting the error "Cannot mix aggeragate and non-aggeragate comparisons in 'if' expressions"
Note: all the prior year data is in my data source as the date 12/2/2011 and all the data for the current month is 7/1/2012
Does anyone have any ideas on how I can fix my formula?
My guess is that [LEW %] is an aggregated field. Therefore, the Termination Date fields need some sort of aggregation. The following KB article should give further insight:
Hope this helps!