February 5, 2003 at 10:48 am
Hi,
I am trying to calculate a variance % column, the calculation below works without the the iif and isempty, however as this calculation seems to calculate the difference between this year and last year actuals, and as i have only two years worth of data, and data which doesn't exist on the years b4 seems to cause an error i.e. 100%, hence using iif and isempty statements. Can someone help me in telling me whether the syntax below is OK and whether you could make any suggestions on getting the results correctly as i'm getting a lot of infinite recursive errors.
iif(ISEMPTY([Fiscal Time April].currentmember),
(([Fiscal Time April].currentmember,[Measures].[Actual])-([Fiscal Time April].prevmember,[Measures].[Actual]))/([Fiscal Time April].currentmember,[Measures].[Actual])
,null)
Thanx
ultimo
February 5, 2003 at 12:40 pm
I think you have to check if the prevmember is not empty. The formula would be:
iif(NOT ISEMPTY([Fiscal Time April].prevmember),
(([Fiscal Time April].currentmember,[Measures].[Actual])-([Fiscal Time April].prevmember,[Measures].[Actual]))/([Fiscal Time April].currentmember,[Measures].[Actual])
,null)
Edited by - mdaniel on 02/05/2003 12:43:28 PM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply