Select Highest

[#1]

Afternoon All,

I'm after some help with an expression,

Below is some sample data regarding a negotiation, unfortunately the data does not provide a correct end point figure (Offer) and instead defaults to the 1st offer.

Order No     Month     Name ID     Direction     Sequence     Offer          Type

1                    Jan          Dan          I                    1                    600          Start

1                    Jan          Dan          O                  2                    450          Neg

1                    Jan          Dan          I                    3                    550          Neg

1                    Jan          Dan          O                  4                    500          Neg

1                    Jan          Dan          I                    5                    600          Accept                 

The information is there although its in the last Direction = O Type = Neg.

The only way I can think of to get to the right figure is to use the sequence number somehow. The problem is it will not be the last sequence

What can I add to the expression below than will only show results for the highest sequence when the Type = Neg.

Sum({$<[Name ID]={‘Dan’},Direction={'O'},Type={‘Neg’}>}Offer)

Thanks

[sunny_talwar]

May be do this:

Dimension: Order No, Month, Name ID, Type

Expression: FirstSortedValue(Offer, -Sequence)

[sinanozdemir]

Maybe something like this:

So you get the latest value based on your criteria. I used RowNo() Max(Sequence) - 1 function in the set analysis.

Hope this helps.

Thanks

[sunny_talwar]

This?

[#5]

Hello,

Thanks for coming back to me but I can’t seem to get this to work, the titles I used in the example are not the actual ones, using your expression but with the actual field names the expression looks like

Sum({<[MJHIST Phase Cache ID]={'Stage2SettlementPackCounterOfferDecision'},[MJHIST Offer Direction]= {'O'},[MJHIST Offer Type] = {'COS'},[MJHIST Offer Sequence Number]={$(=Max(TOTAL Sequence) - 1)}>}[MJLOSS Gross Value Offered])

This returns no data,

Can you see what I’m doing wrong, if I remove the MJHIST Offer Sequence Number]={$(=Max(TOTAL Sequence) - 1) it works, (Although not the way I want it to)

Thanks again

[sunny_talwar]

May be this:

Sum({<[MJHIST Phase Cache ID]={'Stage2SettlementPackCounterOfferDecision'},[MJHIST Offer Direction]= {'O'},[MJHIST Offer Type] = {'COS'},[MJHIST Offer Sequence Number]={$(=Max(TOTAL [MJHIST Offer Sequence Number]) - 1)}>}[MJLOSS Gross Value Offered])

[#7]

It just returns Zero?

[sunny_talwar]

Does this in a text box object -> =Max(TOTAL [MJHIST Offer Sequence Number]) - 1 gives you the sequence number you want to see the result for???

[sasiparupudi1]

what you need is the aggr function here

if you have 3 dimensions in your chart,

$(=aggr(Max([MJHIST Offer Sequence Number]),dim1,dim2,dim3))

in your above example data, the following should give you the correct sequence no

$(=aggr(Max(Sequence),[Order No],[Month],[Name ID],Direction),Type)

hth

Sasi