tag:blogger.com,1999:blog-3689647052791307812.post5846581718112786964..comments2024-01-27T22:41:04.808+11:00Comments on Richard Lees on BI: Getting the last non empty valueRichard Leeshttp://www.blogger.com/profile/05671716466559973540noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-3689647052791307812.post-82612044080430879702016-03-28T22:46:00.608+11:002016-03-28T22:46:00.608+11:00Hi All i have a measure with lastnonempty has an a...Hi All i have a measure with lastnonempty has an aggregation but it is summing up for last 10 days showing grand total for current month .any one have an idea ,why it is behaving like this?Anonymoushttps://www.blogger.com/profile/06773571208524182835noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-64871984648378975462011-04-27T17:45:52.536+10:002011-04-27T17:45:52.536+10:00I want customer list which has value for last none...I want customer list which has value for last nonempty date in month.<br />Example.<br />Customer id is 1,2,3,4<br />Month : March<br />For customer 1,2,3 has value on 30-march.<br />For customer 4 has not value on 30-march but has value on 20-march.<br /><br />I want customer 1,2,3 as result.<br /><br />thanks in advance..Rashminhttps://www.blogger.com/profile/03926066206346597828noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-22741794951286768692010-11-15T22:50:21.397+11:002010-11-15T22:50:21.397+11:00Why don't use anything like this:
TOPCOUNT(no...Why don't use anything like this: <br /><b>TOPCOUNT(nonempty ([Date].[Year Month Day].Members, Measures.Hits), 1).Item(0)</b><br />I think it'll be faster. <br />?Михаилhttps://www.blogger.com/profile/09801768833899307742noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-88284287101110218832010-10-08T06:59:18.304+11:002010-10-08T06:59:18.304+11:00This is because the calculation will find the last...This is because the calculation will find the last nonempty value for the higher level, which will be when any of the lower levels have a value.<br />If you need to support higher levels, you will need to enhance the calculation to aggregate the descendants. ie, put a SUM(DESCENDANTS(MyDim.MyHier,MyDim.MyHier.LowLevel),Measures.NonEmptyCalc).<br /><br />I should warn you that this will add to the query duration. So you should check out performance. As I said above, this functionality is available with SQL Enterprise Edition and it performs much better.Richard Leeshttps://www.blogger.com/profile/05671716466559973540noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-78462165110262251032010-10-08T00:09:32.437+11:002010-10-08T00:09:32.437+11:00Hi! Great solution! It was a long time i was serac...Hi! Great solution! It was a long time i was seraching for it. <br />Only one problem. If i select a hierarchy, I have the right result of my measure in subtotal, but not in the grand total. Why?Giorgiohttps://www.blogger.com/profile/01668186341104260658noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-56788161214025374602010-08-19T05:54:56.251+10:002010-08-19T05:54:56.251+10:00Bitter, it you have different ways of calculating ...Bitter, it you have different ways of calculating at different points in the cube, then you simply need to add that to your calculation. Your calculation knows where it is in the cube, so you can ask it to do different things.Richard Leeshttps://www.blogger.com/profile/05671716466559973540noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-44389461042685226242010-08-18T07:47:45.413+10:002010-08-18T07:47:45.413+10:00I used your this method for a calculation I needed...I used your this method for a calculation I needed to do. it works great, HOWVER on my Totals and subtotals, I am not getting the correct values. it appears to be applying the calculation on the subtotal and total rows rather than summing up the details. anyway to force a summation of the underlying values rather than applying the calcuation on the total and subtotal rows?Bitter Coffeehttps://www.blogger.com/profile/16283582512966375331noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-35960946192464249192010-07-28T09:03:13.627+10:002010-07-28T09:03:13.627+10:00Last question, which will work faster - the LastNo...Last question, which will work faster - the LastNonEmpty in Enterprise or this workaround? or does both of them give the same performance?<br /><br />But have to say, it's a good workaround for people who cant upgrade to Enterprise edition :)Anonymoushttps://www.blogger.com/profile/14392541524727505933noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-10641957509687937112010-07-27T19:38:48.675+10:002010-07-27T19:38:48.675+10:00Hi Boyan,
I don't think Microsoft will worry a...Hi Boyan,<br />I don't think Microsoft will worry about this "workaround". There are many good reasons to use Enterprise, including LastNonEmpty.<br /><br />Jason, there is a cost to the recursive query, but if you have to get the last non empty value, there will be a cost any way you do it. Having said that, I know some people physically load the data for every day, so that you don't have to ask for the last non empty. It really depends on your data and query profiles, which is best.Richard Leeshttps://www.blogger.com/profile/05671716466559973540noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-15867612373741998612010-07-27T16:37:45.308+10:002010-07-27T16:37:45.308+10:00good workaround... but I was just wondering about ...good workaround... but I was just wondering about the performance part. Wouldn't the performance take a hit because of this recursive nature?Anonymoushttps://www.blogger.com/profile/14392541524727505933noreply@blogger.comtag:blogger.com,1999:blog-3689647052791307812.post-75561444397390166282010-07-26T09:31:14.878+10:002010-07-26T09:31:14.878+10:00Clever :) I just wonder how Microsoft would feel a...Clever :) I just wonder how Microsoft would feel about this Enterprise workaround :)asphttps://www.blogger.com/profile/06611947672367978163noreply@blogger.com