Re: Calculate YTD according to a dimension

#1

try something like this:
sum (
{filter(Accounts.AccountLevel.members,
Cint(Accounts.Currentmember.Properties("Account Number")) < 3000),
ytd([Time].currentmember)},[Measures].[Amountmst])

"Henrik Hjøllund" <hjoellund (AT) mail (DOT) tele.dk> a écrit dans le message de news:
ciff01$2pud$1 (AT) news (DOT) cybercity.dk...

Quote:

My scenario is to calculate the YTD differently according to the account
type. Every account with a number < 30000 is a BAL account type while
accounts >= are DR account types. I have build a dimension name LedgerCat
with one level "Ledger Cat". The dimension has an All level and a Ledger
Cat
level with 2 values 'BAL' and 'DRIFT'

I have tried the formula for the YTD:
iif ([LedgerCat].Currentmember.Name = "DR" ,sum
(ytd([Time].currentmember),[Measures].[Amountmst]),
sum(PeriodsToDate([Time].[(All)],[Time].Currentmember),[Measures].[Amountmst
])
)

It does not really do what I want. If I select the value "DR" all accounts
are calculated according to the " sum
(ytd([Time].currentmember),[Measures].[Amountmst]" part of the formula,
also
the < 30000 accounts. And visa versa.

So my question is: how to calculate YTD differently according to the
LedgerCat dimension, so that every account number < 30000 gets calculated
one way and all account numbers >= 30000 get calculated the other way, no
matter how you "turn" the cube.

Thanks in advance
Henrik.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Calculate YTD according to a dimension

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: Calculate YTD according to a dimension - 09-18-2004 , 12:13 PM