How to get vaues corresponding to maximum value?

[#1]

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.

[sunny_talwar]

Try this as your expression:

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

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

[#3]

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

[#5]

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?

[#7]

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

	MonthYear	Total	Total	May 2015	May 2015
CAPP	Region	Sales	Total	Sales	Total
63287325   03/09/2010	Calcutta	400	400	400	400
63287325   04/09/2010	Calcutta	200	200	200	200
63287325   05/09/2010	Calcutta	400	400	400	400
63287325   06/09/2010	Calcutta	400	400	400	400
63287325   07/09/2010	Calcutta	400	400	400	400
63287325   08/09/2010	Calcutta	216.5	216.5	216.5	216.5

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

	MonthYear	Total	Total	May 2015	May 2015
CAPP	Region	Sales	Total	Sales	Total
63287325   08/09/2010	Calcutta	216.5	216.5	216.5	216.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:

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

Best,

Sunny

[#9]

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