Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get vaues corresponding to maximum value?

Hi,

I have data as below in qvw. I need the pivot table should show only values which have Maximum FirstNumber. corresponding values to the Maximum FirstNumber

please see attached sample Input data.

I need out put  as shown in attached Output.

Thanks.

1 Solution

Accepted Solutions
sunny_talwar

Have a look and see if this is what you want

Best,

Sunny

View solution in original post

13 Replies
sunny_talwar

Try this as your expression:

=Only({<FirstNumber = {"$(=Max(FirstNumber))"}>}Sales)

With all other fields as dimension. Output looks like this:

Output.PNG

Not applicable
Author

Thanks. How can I use the same in pivot table?. when I use the expression in pivot tale it is showing blank chart.

Please let me know.

sunny_talwar

Have you looked at the attached qvw? It is a pivot chart and it is not showing blank. I am not sure what exactly you are doing. Can you explain a little more?

Best,

Sunny

Not applicable
Author

Thanks. Sorry I have send this earlier. I need pivot table as in attached

can you let me know how to use your expression in this pivot table please.

sunny_talwar

Not sure what your requirement is, but when I put the formula I gave you, I do see values showing up in the places they are supposed to show up and showing blanks where there are not values. What are you expecting to see?

Output.PNG

Not applicable
Author

for Date = 05/05/2015 there are 6 rows with FirstNumber 0,1,2,3,4,5.

                                                            

MonthYearTotalTotalMay 2015May 2015
CAPPRegionSalesTotalSalesTotal
63287325   03/09/2010Calcutta400400400400
63287325   04/09/2010Calcutta200200200200
63287325   05/09/2010Calcutta400400400400
63287325   06/09/2010Calcutta400400400400
63287325   07/09/2010Calcutta400400400400
63287325   08/09/2010Calcutta216.5216.5216.5216.5

But I need the pivot table should show only one row with Maximum FirstNumber as below

                

MonthYearTotalTotalMay 2015May 2015
CAPPRegionSalesTotalSalesTotal
63287325   08/09/2010Calcutta216.5216.5216.5216.5

I need

Dimension = CAPP

Dimension = Region

Dimension = MothYear

Expression1= Sales

Expression 2 = Sum(Sales)

would it be possible.?

Please help me.

sunny_talwar

Expression 1:

=Only({<FirstNumber = {"$(=Max(FirstNumber))"}>}Sales)

when I select 63287325  08/09/2010 I see exactly what you wanted:

Output.PNG

I am attaching the application for you to review also (PFA)

Best,

Sunny

Not applicable
Author

can you let me know if there are more ID instead of one. how can I get this. please see attached.

sunny_talwar

Check this out: (PFA)

I just worked on Sales Expression. Not sure what you are trying to do in Total.

Best,

Sunny