RE: How to count the cells between CURRENTMEMBER and LastNonEmpty?
- From: wim <wim@xxxxxxxxxxxxx>
- Date: Wed, 15 Apr 2009 08:57:01 -0700
Dear Reed,
Thanks for your suggestions.
In the meantime, I played around a little bit with my cube and its data and
I tested how the cube would behave if I also provide the "missing data". I
built a cube containing the "product was sold" data and also containing the
"product was not sold" data. This is possible because the data I'm working
with is not that much. With this cube then it was very easy to count the
cells between two time members even when slicing is applied.
Best regards
Wim
"Hitachi Reed" wrote:
Hi Wim,.
This is a bit tricky (=interesting) problem. I can explain three options
that seem reasonable but don't work--and tell you the reason each doesn't
work. I can also give you an option that works but is ugly. And I can give
you a short hint to one that may be the best but is too long to explain here.
PROBLEM 1: Slicing doesn't change the members of a set at all, so it will
have no effect on the PrevMember.
Even a sub-select (which does filter the members that turn up in sets in the
main query) doesn't limit member functions like PrevMember to the
sub-select's sub-cube, as you can see if you run the following query on
Adventure Works (happens to be 2008; with slight tweaks to unique names it
would work on 2005).
With
Member T1 as [Date].[Date].CurrentMember.UniqueName
Member T2 as [Date].[Date].PrevMember.UniqueName
Select {T1,T2} on 0 , [Date].[Date].[Date] on 1
FROM ( Select (
[Date].[Day of Week].&[3]
,[Date].[Date].&[20021112]
) on 0
From [Adventure Works] ) ;
This shows that the PrevMember of [20021112] is [20021111], even though the
PrevMember is not in the sub-cube. (Comment out the [20021112] row in the
sub-select, to see that only Wednesdays do show up on rows).
PROBLEM 2: Assuming that your [Day] (which seems to be "Day of Week") is in
the same dimension as [Date] (a day-grained key), then any time you filter on
a specific [Date]. The attribute relationship to [Day] will automatically
change the current member of [Day], making it useless to do any kind of IIF
test such as
With
Member T1 as [Date].[Day of Week].CurrentMember.UniqueName
Member T2 as
IIF ([Date].[Day of Week] IS [Date].[Day of Week].[All Periods],
[Date].[Date].PrevMember
,[Date].[Date].Lag(7)).UniqueName
...
because [Day of Week] will never be [All Periods] unless [Date].[Date] is as
well.
PROBLEM 3: You can't use any trick that employs Exists or EXISTING because
anything that filters the set to include only members with the current [Day
of Week] would also filter the set to include only the current [Date].
UGLY OPTION: One solution--given the dimension assumptions I stated
above--is to just create two calculated measures and force the user to select
the appropriate one.
HINT AT BETTER WAY: A better solution would be to create another instance of
the Date dimension in the cube--hiding all but the [Day of Week] attribute,
and not linking it to any dimension. You could then use this dimension to
allow the user to select the day of the week, and then you have various
options--such as using an IIF formula. I have a blog almost ready to post
that shows how to use this "unlinked" dimension concept to control the number
of periods in a Rolling Sum calculation. Once I get it posted, I'll respond
with a url.
Good luck.
Reed
"I may work for Hitachi Consulting, but my dumb ideas are all mine."
"wim" wrote:
Hi,
To explain my problem consider the following scenario:
I have a cube with
- a time dimension
- a product dimension
- a sales amount measure (a product is selled on a day)
Now, I would like to determine the "distance" (in existing cells) between
the time.CURRENTMEMBER of a specified product and its last sale. Example:
time.CURRENTMEMBER for product A is wednesday. The last sale for this product
was on monday. Thus, the distance should be 2 (days) -> product A was not
selled the last two days (if no slicing is applied).
So, I think this is not really a problem so far. But how can I achieve this
if I have to consider any slicing?
Same problem as described before but I only want to see wednesdays (Cube is
sliced by [Time].[Day].&[Wednesday]). Example: time.CURRENTMEMBER for product
A is wednesday. The last sale for this product was on monday and the sale
before on wednesday last week. In this case since I am only looking at
wednesdays I want to get a result of 1 (the PREVMEMBER in this slice is
wednesday of the last week) and not 2 (as the last real sale was on monday)
and not 7 (as the last wednesday is seven days ago).
I have already solved this problem for queries without slicing. But when it
comes to slicing my calculated member always counts any item/cell in the
range between last visible sale and time.CURRENTMEMBER without taking into
account the applied slicing condition. For the example as described above it
would return 7 as it counts the 7 days in this range and not 1.
Does anybody have any ideas how I could achieve my desired results?
Thanks for your help in advance.
Wim
- References:
- RE: How to count the cells between CURRENTMEMBER and LastNonEmpty?
- From: Hitachi Reed
- RE: How to count the cells between CURRENTMEMBER and LastNonEmpty?
- Prev by Date: Problem with standard deviation calculation
- Next by Date: RE: Drillthrough Calculated member in cube
- Previous by thread: RE: How to count the cells between CURRENTMEMBER and LastNonEmpty?
- Next by thread: Drillthrough Calculated member in cube
- Index(es):
Relevant Pages
|
