Post reply

Finding the first non empty previous member

  • November 18, 2008 at 10:41 am

    #196544

    Hi,

    I need to perform a calculation where I substract the value of my current member from the value of the previous member. However sometimes the previous member is empty, in which case, I want to use the first non empty previous member available.

    This is a slimmed down copy of my query. I've been playing around with various functions like parallelperiod, prevmember, isempty etc... but haven't managed to work it out yet. Any help appreciated!

    with

    MEMBER [measures].[subscriptions previousMember] as

    ([measures].[subscriptions],parallelperiod([time].[time].[month],1,[time].[time].currentmember))

    MEMBER [measures].[subscriptions change] as

    '[measures].[subscriptions] - [measures].[subscriptions previousMember]'

    SELECT non empty {[Time].[Time].[2008].[Q3].[sep]:[Time].[Time].[2013].[Q4].[dec]} on columns,

    {[measures].[subscriptions],

    [measures].[subscriptions previousMember],

    [measures].[subscriptions change]} on rows

    FROM [subscription facts]

    Thanks, Jon

  • November 21, 2008 at 10:04 am

    #901738

    We worked this out in the end using a nice little recursive call.

    member [measures].[lastnonempty] as

    case when not isempty(([measures].[price], [time].[time].prevmember))

    then ([measures].[price], [time].[time].prevmember)

    else [time].[time].prevmember

    end

  • November 21, 2008 at 8:19 pm

    #901953

    nicely done. thanks for posting the solution. it is always nice to see this type of contribution to the forums and much nice than just seeing a 'we fixed it' or 'figured it out' or 'problem solved'. nice to see a little resolution for a change and great job. now it would only be nice if you could mark a response as the solution like the microsoft forums...or maybe you can i just don't know how you do it.

    ----------------------------------------------------------------------------------------
    Dan English - http://denglishbi.wordpress.com

  • November 24, 2008 at 2:49 am

    #902335

    Thanks. It turns out there are a few different ways to do this depending on your situation. If you have the SQL Server Enterprise Ed you can use the LastNonEmpty semi-additive measure. Or you can use some of the suggestions posted here by Bill Pearson in a response to the same question.

    http://forums.databasejournal.com/showthread.php?p=122806#post122806

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply