topic Re: Select Highest in QlikView

Select Highest

Thu, 13 Aug 2015 15:12:26 GMT

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

Re: Select Highest

sunny_talwar — Thu, 13 Aug 2015 15:32:28 GMT

May be do this:

Dimension: Order No, Month, Name ID, Type

Expression: FirstSortedValue(Offer, -Sequence)

Re: Select Highest

sinanozdemir — Thu, 13 Aug 2015 15:38:26 GMT

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

Re: Select Highest

sunny_talwar — Thu, 13 Aug 2015 15:45:18 GMT

This?

Re: Select Highest

Mon, 17 Aug 2015 11:33:08 GMT

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

Re: Select Highest

sunny_talwar — Mon, 17 Aug 2015 11:51:28 GMT

May be this:

Re: Select Highest

Mon, 17 Aug 2015 11:57:08 GMT

It just returns Zero?

Re: Select Highest

sunny_talwar — Mon, 17 Aug 2015 12:01:18 GMT

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???

Re: Select Highest

sasiparupudi1 — Mon, 17 Aug 2015 12:26:26 GMT

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