September 11, 2009 at 8:55 am
hi
i have a cube with trades in it and the tradeID is the primary key in the main table....
i have a measure that counts the trades, easy, it works
what i want is a measure that counts the trade only if it is the first instance of a trade with a certain structureID
if i do a distinct count measure...
distinctcount([TradeID].[StructureIDHierarchy].[StructureID])
then it comes up with the right total...but if i slice and dice by time, it naturally comes up with a count in all the months it's traded...and also the numbers dont add up to the total....
e.g.
TradeID, StructureID,DealDate
abc12,aaaaa1,1/1/2000
abc13,aaaaa1,1/12/2000
gives a distinct total of 1, but 1 in january and 1 in december
fair enough, not what i'm after, but close. i'm after a 1 in january, a 0 in december and a 1 total
any ideas how to do this in MDX?
thanks in advance
September 16, 2009 at 5:23 pm
I don't really understand what you are trying to do, because you had one row in Dec.
CMIIW ...
When you browse the cube in Time.Year level you want to see 1 in January and 0 in December. That means you want the display 1 for the first child and 0 for the others.
In MDX, just simply check the member. If member is a first child then display 1, other display 0. Use the IIF function.
September 17, 2009 at 2:55 am
Hendra Eka Putra (9/16/2009)
I don't really understand what you are trying to do, because you had one row in Dec.
you misunderstand, i want a 1 in january as that is where the first component trade of a certain structureID occurs, not because it's the first month of the year
don't worry though, i gave up trying to do it in MDX and did it in SQL further upstream with an SP and a new field
thanks anyways, would be interested if anyone has something similar and solves it in MDX...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply