to get the latest price value

to get the latest price value

• cmatt

  hello all again,

  just by chances if anyone still can further help ..... bcoz there is still a problem remained..

  I found if there is no value from current month, the sample from Chris would not return any value from previous month..   I think it's about the DESCENDANTS function that only retunn to the level of current month or day.. I try further furrnish it but not success yet.

  as known, the situation is to have product's latest price be shown... such as:
                                      jan         feb        mar           apr
  product name 1       latest price
  product name 2
  product name 3

  There might not be transactions in APR but then should have to get the price
  from MAR or before..

  So.. see if any advice further.

  cheers, Andy

  
  
  

• oldguy1166

  Can you please provide the current version of the MDX you are using.  Please include the calculated member MDX and the SELECT statement MDX.

  - Steve

  
  
  

• hpannu

  oh Steve...

  i believe that's a very useful trick... i also tried to find out something simliar but in the "Aggregate Function" inside the Cube Editor .. it list out the Sum, Count, Min, Max, and Distinct Count.  Thereis no LastNonEmpty..

  Is that in SQL 2005 only i'm still in sql2000.. .(bcoz system installation from before)

  Can you point me where to find the "LastNonEmpty" if it's aail in SQl2000..

  any more material i can reference to ..

  regards, Andy

   

  
  
  

• loanwolf35

  I think all of the contributors to this forum do have a job besides posting here... But sure, it's not ONLY PERSONAL interest
  
  

• Pat Carden

  hi Steve ,

  very happy to get ur fast response... i'm new to MDX and so may be some more questions hope u can help (actully not new... but MDX is not easy for me )

  base on ur suggestion, i'm writing the MDX script (I'm still using sql2000 however ) as below

  with

  set [lastprice] as '

  (tail (filter (  [time].members, measures.[unit price] > 0), 1).item(0).item(0), measures.[unit price])'

  select

  [lastprice] on columns,

  from [transactions]

  However, i get the error "cannot convert member to Set - in a <tuple> base object"..  any idea of what's going wrong..

  Additionally, (may be i post the whole requirements so make sure myself not to go wrong direction), what i actually need is "per each product, a sum of all sales unit in the year, multiply the last price value"...

  i may be able to get the sales unit aggregation sum by YTD function.. .. .so I'm now to get the last price value per a product..

  very apprecipate fast response... just unexpected that fast.. : 

  Andy

  
  
  

• BENL_PERFINEX

  hi steve, cool....

  i believe that's exactly the all right things for me further to work on ..

  really many thanks for ur kindness help..

  cheers, andy

  
  
  

• MyNameIsKen

  i'm sorry one more question as i'm testing out the senarios...

  i'm trying to get a result of

                              jan          feb             mar              apr             may
  fund name A      price
  fund name B      price

  where price should be the latest one for that period (month) ... .. I try to develop the member .. but it returns all #ERR...

  the script i'm using is...

  WITH Member Measures.[lastprice] as

  '(tail (filter ( ( [time].[2005] : [time].currentmember, measures.[unit price] > 0), 1).item(0).item(0), measures.[unit price])'

  the script runs but all return is #err..

  thanks again

  Andy

   

  
  
  

• suroma

  Andy,

  I think you are there, we just need to change your query scoped "set" to a "member".

  Try the following:

  WITH Member Measures.[lastprice] as

  '(tail (filter (  [time].members, measures.[unit price] > 0), 1).item(0).item(0), measures.[unit price])'

  SELECT

  { [Product].[Some product member from your product dimension] } on rows,

  { [Time].[Some time member from your time dimension] } on colums

  FROM [transactions]

  WHERE (Measures.[lastprice] )

  This query will show the last price for the product for the time specified, which I hope is what you are looking for.

  HTH,

  - Steve

  
  
  

• HofCompSciStudent

  hi steve,

  thanks a lot....  i get the idea now and that's almost exactly what i need .......... many thanks..

  i got some questions to further furnish.. .See if appropriae in this thread..

  1) there could be chances that there are two transactions in the same day for a product, thus two price info (they may be different).  The above statement would get the latest day, but it have aggregate sum value of the PRICE.   (e.g . both transactions of date 1/20/2005...  it's not getting the last record by TAIL function.. i'm not sure how it calculates.. )

  except to modify the date value to have distinct value (say 1/20/2005 1.am, 1/21/2005 2.am, etc)... anyway to get the latest price but only 1 value return even same day.

  2) try to specify a period for getting the latest price.. as:

  WITH Member Measures.[lastprice] as

  '(tail (filter (  [time].[2005].[q1].children, measures.[unit price] > 0), 1).item(0).item(0), measures.[unit price])'

  in this case, return the aggregate sum value of a product of all prices in FEB (even they are all in distinct days)

  e.g.

  2/10/2005  10.5
  2/11/2005   12.5

  the return result is 23  rather 12.5  (but by original statement, the return value is 12.5... ) why after specifying the period..olap returns aggregate.. ...

  3) try to specify the lower level in time .. ie.. Month ... e.g. Feb

  WITH Member Measures.[lastprice] as

  '(tail (filter (  [time].[2005].[q1].[feb].children, measures.[unit price] > 0), 1).item(0).item(0), measures.[unit price])'

  but there is no value return.. what should be specified for Feb

  Hope that's all about my questions..
  (what is HTH... Steve.. are u representative of MS )

  best regards
  andy

  
  
  

• LJames

  Andy,

  Just because I'm courios... Did you try swithing the "sum" aggregation function to "LastNonEmpty" I guess that should do the trick without any MDX coding... Your problem is quite the same as the "inventory problem"... The stock is calculated not by a sum (sum up my inventory...) but by the last information you have (what is the newest stock count)... I guess that's the same with your example... The "inventory problem" is solved by using "LastNonEmpty" (returning the last known value)...

  HTH (means "hope that helps")

  
  
  

• patricef

  Sorry, I didn't see that you still use 2000... Have a look at this, I can't explain it better...http://groups.google.de/group/microsoft.public.sqlserver.olap/browse_frm/thread/a8eb010208af987f/51107c0f9c002d16

   

  
  
  

• maria_tachi

  ...no problem... By the way: I'm Thomas... Sorry for jumping into your discussion...
  
  

• Brian Benjamin

  Andy,

  Here is a calculation that I added to the "AdventureWorks" cube to return the latest "Internet Sales Amount" by date for any position in either the Calendar or Fiscal hierarcy:

  Create Member CurrentCube.[Measures].[Latest Internet Sales Amount]

  AS

  (Tail(Filter(Existing [Date].[Date].Members, Measures.[Internet Sales Amount] > 0),1).Item(0).Item(0),Measures.[Internet Sales Amount]),

  Format_String = "Currency",

  Non_Empty_Behavior = { [Internet Sales Amount] }

  I added a filter statement to get rid of dates where there is no value for internet sales, but you may not need to do this unless your time dimension includes dates where there is no data in the cube.  I think that this is what you are after and hopefully this example will help you to create the calculation you are looking for.

  HTH,

  - Steve

  
  
  

• Jeremy Balliston

  oh hi thomas..

  ha .. sorry not notice that... and ur jump in is much appreciapted of course.  U both are helpful.. best wishes!

  (u are all assigned to monitor this group... or just for personal interest..  ... So kind!)

  cheers, Andy

   

  
  
  
• to get the latest price value