April 9, 2003 at 9:45 am
Hi all, I am currently trying to create a calculated member on a cube. It should be quite straight forward as all i want is a sum of a field when it is equal to 1. i create the measure as a sum, then create a new calculated member and use "(Iif([Measures].[Episodenumber]=1, 1, 0))" but it doesn't work. What am I doing wrong.
Please help!
April 10, 2003 at 6:20 pm
Hi Michael,
A quick guess on your data : Are you trying to get a count of "visits" where a visit is defined to have occured only on the first episode? ie a visitor can have multiple episodes but are to be counted as visiting only once regardless of episode count.
If so, there's a couplke of ways of acheiving this, a very simple one is to add to your source view (or table, but using a view could be nice way of not modifying source table but getting what you want). Say current view is "SELECT * FROM tblEpisode", mod this to "SELECT *, CASE EpisodeNumber WHEN 1 THEN 1 ELSE 0 END FROM tblEpisode"
This should give you all of your current columns, plus a new "count" column that will only have ones(1's) for the first episode. This can then be easily added as a simple sum measure to your cube.
Alternatively, you could use a calc member within the model, but based on your earlier post I need a little more info on the cube structure to help out, as your MDX looks ok to me. One thing that is "wrong" with it is that if your cube has > 1 measure (ie EpisodeNumber is not the only measure) then when using this calc member and a different member would produce unreliable results.
Hope this helps.
Steve
Steve.
April 14, 2003 at 4:52 am
Hi Steve,
Just read your message so I haven't tried it yet but your correct in your understanding of the problem. Using a view is agreat idea thanks. I'll try it and i'm sure it will sort this problem out.
Also I do have several measures so this explains why my mdx was going wrong.
Thanks a million.
Michael.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply