Re: mdx: last valid price

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You can do this using a recursive calculation, it might take a while to
run, but it should be possible to do using something like

WITH MEMVER [Measures].[LastPrice] as 'iif([Measures].[Price]=0,
(Time.PrevMember,[Measures].[LastPrice]),Measures.[Price])

Note that the member refers to itself in the formula, this is what makes
the recursion work.

This is a very basic example, if you need this calculation to work over
all the hierarchies of the time dimension there are other things you
would need to add to get this to work, but this should solve the issue
you outlined in your original post.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1126617339.420846@xxxxxxxxxxxxxxxxxx>, ramblk2@xxxxxxxxxxx
says...
> Phil,
>
> thanks you for you answer. But this solution does not work for me :).
> Answer to your prev notes: the price can change when ever, several times in
> month, or it could not change for a months. If the price did not change in
> current month or day, calc member must show the last valid price.
> The logic should be like:
>
> if the Price is not null, show me exactly this value, else show the prev not
> null value.
>
> Additional help would be very appreciated.
>
> Ramunas
>
>
> "SQL McOLAP" <SQLMcOLAP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:ECB2FA82-9680-49A4-8096-1A4CF27A94A0@xxxxxxxxxxxxxxxx
> > Right after I wrote this, I saw an answer Pras had for a similar problem
> for
> > inventory. Doing a tail of a filtered set could do this:
> >
> >
> Tail(Filter(Descendants([Time].currentmember),([Measures].[Price])>0),1).ite
> m(0)
> >
> > ..although it could be slow if there are a lot of records.
> >
> > Good luck.
> >
> > (and thanks Pras!!!)
> >
> > - Phil
> >
> > "SQL McOLAP" wrote:
> >
> > > Ramunas -
> > >
> > > I'm trying to think of a way this can be done natively in the cube and
> > > nothing jumps out at me. Is there a pattern, like a specific day of the
> > > month a price can change for an item? Or can it happen at any point?
> If it
> > > can happen at any point, this will be difficult in MDX. LastSibling and
> > > LastChild and ClosingPeriod only work for the last member or child of a
> > > member. If these prices can change at anytime, it might be difficult.
> > >
> > > My first suggestion, while trying to figure out an OLAP solution, would
> be
> > > to create a column in your SQL table or view called "last valid price"
> and

.

Relevant Pages

  • Re: mdx: last valid price
    ... Ramunas Balukonis ... > You can do this using a recursive calculation, it might take a while to ... >> Answer to your prev notes: the price can change when ever, ... If the price did not change ...
    (microsoft.public.sqlserver.olap)
  • Re: San Diego Visit
    ... Present your Auto Club Membership card at any ticket booth to receive ... Up to tickets may be purchased per member. ... San Diego Zoo:AAA members show your membership card to save 10 percent ... ask for the special Adults at Kid's Price Kids 1/2 Price offer. ...
    (rec.travel.usa-canada)
  • Re: PUG Loot Ettiquette (Was - Re: Excellent PUG tonight)
    ... used by any member of the party. ... You don't think a shard or money will be used by any member of the ... and can vendor almost anything. ... And you're going to try to tell me that your 3g vendor price (or some other ...
    (alt.games.warcraft)
  • Re: But There Aint No Global Warming.
    ... Can you name anyone who wants oil but can't buy ... The price is artificially high right now because speculators, ... Member #33 1/3 of The "I Have Been Killfiled By Tommy" Club ... "There's a sucker born every minute" - David Hannum (often ...
    (sci.energy.hydrogen)