September 28, 2005 at 7:56 pm
I want to query cube data using Excel pivot tables, and have two calculated members for comparing revenue from this year against last year. They are:
Name: [Revenue Increase]
Value: [This Year Revenue] - [Last Year Revenue]
Name: [Revenue Increase %]
Value:
IIf([Measures].[Last Year Revenue]=0,0,[Measures].[Revenue Increase]/[Measures].[Last Year Revenue])
I have used the IIf statement to avoid division by zero errors. The problem is that empty cells all convert to zero. In the Excel pivot table the Revenue Increase field works fine - it just gives me the data I want to see. However if I add the Revenue Increase % field I get all the possible dimension members, with most of them showing nothing but zero in that column.
I know in MDX queries you can eliminate the unwanted calculations using the Filter function, but how can I stop this calculation being made on empty cells for use in Excel?
Mark.
September 28, 2005 at 9:37 pm
In a way, isn't it your formula that's forcing the inclusion of these members by specifying zero as the first possible value for the statement (ie now making the cells 'not empty')? Have you tried changing it to be null instead of zero to see if this will keep the empties suppressed?
IIf([Measures].[Last Year Revenue]=0, null,[Measures].[Revenue Increase]/[Measures].[Last Year Revenue])
Steve.
September 28, 2005 at 10:18 pm
My formula was based on one of William Pearson's articles in DatabaseJournal.com
I'm new to this game and didn't realise you could put "null" in there. I had tried leaving it blank (i.e. =0,,[Measures ...) but that did not work.
Thank you Steve. Your solution works just fine.
Mark.
September 28, 2005 at 10:22 pm
Bill has produced some excellent articles/primers, it's a good place to take a lead from re:formula's and general approaches to getting things done with AS and RS. Glad yo got it to work!
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply