hello all..
having been search around for days but no clue.. Hope some MVP can help..
typically from transaction, there are TIME dimension, PRODUCT dimension, and there is PRICE value measure.
normally, OLAP returns is the sum aggregation of the PRICE value when cross joining the TIME and PRODUCT. However, I would like to retreive only the LATEST PRICE of a product..
return result should be:
PRODUCT LATEST PRICE
ABC 10.5
CDE 12.5
etc
how can i achieve this.. I'm trying to use TAIL function but not success in doing that yet.. hope some people can help me .... quite urgent for customer..
regards, Andy
to get the latest price value
Sandoc
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
EgoStripper
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
kdot
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
Brennon
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
Byron
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
mac_higa
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
walemems
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
TSCEWA
billinares
ErikLidman
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
Itay Sagui
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
JeremyAtGosub
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
Olof Harwell
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
BoonUnit
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")