Tracking cumulative "Days owned" - MDX Newbie

#1

1 - If the TransDate dimension does not have an (All)
level you can create one. Then you can update your formula
to:

RT=Sum(PeriodsToDate( [TransDate].[(All)]), [Measures].
[Owned] )

2 - You can try :

Sum(PeriodsToDate( [TransDate].[(All)]), [Measures].
[RT] )

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

Quote:

-----Original Message-----
Hi All

I have a cube that contains a Quantity Owned field. This
field records only
the changes in the Quantity. I need to do two things with
this:-

1. Build a running total of the quantity owned so that I
know how many I own
on any given day. The expression I use for this is
Sum(PeriodsToDate( [TransDate].[Year]), [Measures].
[Owned] )

Which works fine in the year that quantity changes occur,
but in the
following year its starts from zero again. Clearly, I've
got it wrong !

2. I need a running total of the expression (quantity *
days owned). So, if
I buy 10 on Jan 1st, then the values for the column might
look like the
following:-

Jan 1 10
Jan 2 20
Jan 3 30
:
:
Jan 31 310
:
:
Dec 31 3650
Jan 1 3660
etc

This one is proving harder. I Have...

iif([TransDate].CurrentMember.Level.Ordinal = [TransDate].
[Day].Ordinal,
Sum({ [TransDate].FirstSibling :
TransDate].Currentmember},[Measures].[Cumulative Owned
Qty]),
iif([TransDate].CurrentMember.Level.Ordinal =
[TransDate].[Month].Ordinal,
Sum (Descendants([TransDate],[TransDate].[Month],
AFTER),
[Measures].[Cumulative Owned
Qty]),
iif([TransDate].CurrentMember.Level.Ordinal =
[TransDate].[Year].Ordinal,
Sum (Descendants([TransDate], [TransDate].
[Year],
AFTER),[Measures].[Cumulative Owned Qty]),
NULL)))

....which came about by trial and error and relies on the
cumulative owned
quantity that I already know I'm not calculating
correctly :-(
I'm fairly sure that the above would work if the
cumulative owned qty
measure was working, but I wonder if I could have
achieved my aim more
simply.

Can anybody clue me in to what I'm doing wrong?

Thanks

Malcolm Burtt

.

#2

You're right. I hadn't really checked that (it helps if
problems are described as Foodmart 2000 queries).

I believe this should do the trick:

Sum(PeriodsToDate( [TransDate].[(All)] , ClosingPeriod
([Day])), [Measures].
[RT] )

HTH,
Brian

Quote:

-----Original Message-----

"Brian Altmann" <brianaltmann (AT) yahoo (DOT) com> wrote in message
news:0b5501c368ad$a6dc1d20$a401280a (AT) phx (DOT) gbl...
1 - If the TransDate dimension does not have an (All)
level you can create one. Then you can update your
formula
to:

RT=Sum(PeriodsToDate( [TransDate].[(All)]), [Measures].
[Owned] )

2 - You can try :

Sum(PeriodsToDate( [TransDate].[(All)]), [Measures].
[RT] )

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

-----Original Message-----
Hi All

I have a cube that contains a Quantity Owned field.
This
field records only
the changes in the Quantity. I need to do two things
with
this:-

1. Build a running total of the quantity owned so that
I
know how many I own
on any given day. The expression I use for this is
Sum(PeriodsToDate( [TransDate].[Year]), [Measures].
[Owned] )

Which works fine in the year that quantity changes
occur,
but in the
following year its starts from zero again. Clearly,
I've
got it wrong !

2. I need a running total of the expression (quantity *
days owned). So, if
I buy 10 on Jan 1st, then the values for the column
might
look like the
following:-

Jan 1 10
Jan 2 20
Jan 3 30
:
:
Jan 31 310
:
:
Dec 31 3650
Jan 1 3660
etc

This one is proving harder. I Have...

iif([TransDate].CurrentMember.Level.Ordinal =
[TransDate].
[Day].Ordinal,
Sum({ [TransDate].FirstSibling :
TransDate].Currentmember},[Measures].[Cumulative Owned
Qty]),
iif([TransDate].CurrentMember.Level.Ordinal =
[TransDate].[Month].Ordinal,
Sum (Descendants([TransDate],[TransDate].
[Month],
AFTER),
[Measures].[Cumulative Owned
Qty]),
iif([TransDate].CurrentMember.Level.Ordinal =
[TransDate].[Year].Ordinal,
Sum (Descendants([TransDate], [TransDate].
[Year],
AFTER),[Measures].[Cumulative Owned Qty]),
NULL)))

....which came about by trial and error and relies on
the
cumulative owned
quantity that I already know I'm not calculating
correctly :-(
I'm fairly sure that the above would work if the
cumulative owned qty
measure was working, but I wonder if I could have
achieved my aim more
simply.

Can anybody clue me in to what I'm doing wrong?

Thanks

Malcolm Burtt

.

Hi Brian

Your fix for my first question worked a treat, thanks,
but the second one
only works if you drill down to the day level. So, at Day
level you see...

Jan 1 10
Jan 2 20
Jan 3 30
:
Jan 31 310

But at Month level we see...

Jan 10
Feb 20
Mar 30
:
Dec 120

My solution is actually giving me the right results at
all levels now that I
have the fix for cumulative quantity from you, so I'm
happy :-), but it
still seems like an awful lot of effort and probably
performs like a dog.
:-(

Regards

Malcolm.

.

#3

"Brian Altmann" <brianaltmann (AT) yahoo (DOT) com> wrote

Quote:

You're right. I hadn't really checked that (it helps if
problems are described as Foodmart 2000 queries).

I believe this should do the trick:

Sum(PeriodsToDate( [TransDate].[(All)] , ClosingPeriod
([Day])), [Measures].
[RT] )

HTH,
Brian
SNIP

Thanks, Brian that worked a treat and is far more compact than my original
solution. I'll also take your FoodMart 2000 point on board and try and use
it in any future problems I post.

Regards

Malcolm

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

Tracking cumulative "Days owned" - MDX Newbie

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

Tracking cumulative "Days owned" - MDX Newbie - 08-22-2003 , 08:02 AM

Re: Tracking cumulative "Days owned" - MDX Newbie - 08-22-2003 , 11:58 AM

Re: Tracking cumulative "Days owned" - MDX Newbie - 08-26-2003 , 04:59 AM