Pardon my newbie questions.

I think I need a calculated measure that involves Time Dimension (y/q/m/d),
linked to (any/selected) measures, that shows a difference between two
adjacent time slices.

Something like this (from the perspective of Excel & Pivot Table):

Client Time
2003
Quarter2 Quarter3 Variance Quarter4 Variance
A 45 51 6 55
4
B 39 37 -2 38
1
....

Is anything like this possible without involving RDB to begin with? Note: I
use SQL7 OLAP.

TIA

lc

You can use the prevmember function, as in:

(time.currentmember,measures.[my measure]) -
(time.prevmember,measures.[my measure])

If you have many measures and you don´t want to define a
calc measure for each one then you would need an extra
dimension (say Periodicity] with one stored member, say
Regular, and a calc member such as:

(time.currentmember,periodicity.regular) -
(time.prevmember,periodicity.regular)

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

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

This works perfectly. Thanks a million.

I don't think I understand (yet), but will try a few things before I'd abuse
your time again.

Thanks a lot.

Oh, yes, Big Time.

lc

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

Hmmm, this didn't work after all. So, I added to my fact table a varchar
field and filled it value of 'Variance'. Then I created a dimension
(Variances) out of it and used it in calc member formula:

(time.currentmember, [Variances].[All Variances].[Variance])
- (time.prevmember, [Variances].[All Variances].[Variance])

All I get is error in that field. What did I miss (if it's obvious to you)?

Thanks a lot.

lc

Sorry I was not clear, of course you need the extra field
in the fact table to link to the extra dimension.
I prefer to create the dimension ([Variances] or
Periodicity) out of a one-record table instead of creating
it out of the fact table column because the design is
clearer and processing is more efficient.
Still your solution should work as long as the calc member
belongs to the [Variances] dimension.
HTH,
Brian

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

I had a hunch that might be a problem so I went ahead and created a
one-record table just for this. Unfortunately, this didn't do it either.
Perhaps it's my design of this Periodicity dimension. What I did:

new table Periodicities( PerCode int, PerDescr varchar( 11 ) )
insert into Periodicities values( 1, 'Periodicity' )
created a shared dimension Periodicity
added PerCode to the fact table
created a calc field (Variance) that looks like:

( [WE Dates].CurrentMember, [Periodicities].[All
Periodicities].[Periodicity] )
- ( [WE Dates].PrevMember, [Periodicities].[All
Periodicities].[Periodicity] )

Still getting errors in Variance field. Do you have any idea what am I
missing?

Thanks a lot for your time Brian.

lc