Date dimensions

#1

Try something like:

tail(filter(date.day.members, not isempty([day
balance])),1).item(0)

This should be a member of the Date Dimension.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html

Quote:

-----Original Message-----
Im trying to create a cube in the cube editor but i cant
work out how to get the value in the last valid cell of a
month.

Say I have the following

1st Jan 2nd jan 3rd Jan 4th jan

CustomerA 10.00 20.00 20.00 5.00
CustomerB 50.00
CustomerC 30.00 40.00

What Im trying to do is create a calculated member that
returns the last value for the customer.

Ive been using :-
([Measures].[DayBalance],closingperiod([Date].[Day],
[Date].currentmember))

However this returns the value for the last day ie:
CustomerA = 5.00, CustomerB =<empty>, CustomerC =<empty

What I want is for the calculated member to return the
following. CustomerA = 5, CustomerB = 50, CustomerC = 40

Can anyone help me ?

Thanks,
John.
.

#2

Thanks for the sugustion, however i cant get it to work.
I get an infinate recursion error on the calculated
measure when using the calculated member.

Any ideas what the problem is ?

Thanks,
John

Quote:

-----Original Message-----
Try something like:

tail(filter(date.day.members, not isempty([day
balance])),1).item(0)

This should be a member of the Date Dimension.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html

#3

When I try it in Foodmart it works fine as long as you
make sure that the calculated member's parent dimension is
[Date] in Calc Member Builder. If it is Measures then you
get an infinite recursion error.
HTH,
Brian

Quote:

-----Original Message-----
Thanks for the sugustion, however i cant get it to work.
I get an infinate recursion error on the calculated
measure when using the calculated member.

Any ideas what the problem is ?

Thanks,
John

-----Original Message-----
Try something like:

tail(filter(date.day.members, not isempty([day
balance])),1).item(0)

This should be a member of the Date Dimension.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html

.

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

Date dimensions

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

Date dimensions - 12-11-2003 , 11:17 AM

Date dimensions - 12-12-2003 , 03:33 AM

Date dimensions - 12-12-2003 , 06:44 AM