Day Count Between LastTransaction Date and Current Date
i have 4 measure member
1. [max transaction date] calulated
2. [currentmember] - current date (calculated)
3. daycount - measure dericed dimdate
4. [duecount] [error part]
belllow code works fine these 3 measure.
now issue part
**************************************
member [date].[date].[duecount]
as sum({([date].[date].[max transaction date]): membertostr([date].[date].[currentmember])})
select {([date].[date].[duecount],[measures].[daycount])} on rows
on columns,
[vendor].[vendor].members
on rows
from [vendor ledger]
error msg :
cellordinal 0
value #error calculated member cannot used operand of range operator ( : ).
formatted_value #error calculated member cannot used operand of range operator ( : ).
****************************************
but this code works because there no calculated member
[date].[date].[my range] as 'sum(({[date].[date].&[2005-01-01t00:00:00]: [date].[date].&[2005-01-04t00:00:00]}))'
select
([date].[date].[my range],[measures].[daycount] ) on columns,
[vendor].[vendor].members on rows from [vendor ledger]
this code works except due count
with member [date].[date].[max transaction date] tail(filter([date].[date].members, not isempty([measures].[amount]))).item(0).name
member [date].[date].[currentmember] strtomember("[date].[date].&[" + format(now(), "yyyy") + "-" + format(now(), "mm") + "-" + format(now(), "dd") + "t00:00:00]" ).name member [date].[date].[duecount] sum({([date].[date].[max transaction date]).member_name : membertostr([date].[date].[currentmember]).member_name }) --[date].[date].&[2005-01-06t00:00:00] select { ([date].[date].[max transaction date]) ,([date].[date].[currentmember]) --([date].[date].[duecount],[measures].[daycount]) } on columns, [vendor].[vendor].members on rows [vendor ledger]
result above code:
2009-03-16 00:00:00 2009-05-08 00:00:00
2008-03-31 00:00:00 2009-05-08 00:00:00
2008-03-31 00:00:00 2009-05-08 00:00:00
2009-02-27 00:00:00 2009-05-08 00:00:00
can 1 me counting [duecount] (calulated date dimension members count)
thanks
is error message says, ":" not work calculated members
because calculated members not part of hierarchy , cannot define range based on them
simle workaround not use calculated member range operator use value calculated member based on directly
with member [date].[date].[duecount] sum({tail(filter([date].[date].members, not isempty([measures].[amount]))).item(0) : strtomember("[date].[date].&[" + format(now(), "yyyy") + "-" + format(now(), "mm") + "-t00:00:00]"},hth,
<your measure>
) --[date].[date].&[2005-01-06t00:00:00] select { ([date].[date].[max transaction date]) ,([date].[date].[currentmember]) --([date].[date].[duecount],[measures].[daycount]) } on columns, [vendor].[vendor].members on rows [vendor ledger]
gerhard
- www.pmone.com -
SQL Server > SQL Server Analysis Services
Comments
Post a Comment