topic Re: How to get vaues corresponding to maximum value? in QlikView

How to get vaues corresponding to maximum value?

Wed, 13 May 2015 15:19:38 GMT

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.

Re: How to get vaues corresponding to maximum value?

sunny_talwar — Wed, 13 May 2015 15:31:24 GMT

Try this as your expression:

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

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

Re: How to get vaues corresponding to maximum value?

Wed, 13 May 2015 15:43:15 GMT

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.

Re: How to get vaues corresponding to maximum value?

sunny_talwar — Wed, 13 May 2015 15:45:36 GMT

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

Re: How to get vaues corresponding to maximum value?

Wed, 13 May 2015 15:55:45 GMT

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.

Re: How to get vaues corresponding to maximum value?

sunny_talwar — Wed, 13 May 2015 16:00:20 GMT

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?

Re: How to get vaues corresponding to maximum value?

Wed, 13 May 2015 16:06:48 GMT

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.

Re: How to get vaues corresponding to maximum value?

sunny_talwar — Wed, 13 May 2015 16:11:43 GMT

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

Re: How to get vaues corresponding to maximum value?

Wed, 13 May 2015 16:31:44 GMT

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

Re: How to get vaues corresponding to maximum value?

sunny_talwar — Wed, 13 May 2015 17:19:01 GMT

Check this out: (PFA)

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

Best,

Sunny

Re: How to get vaues corresponding to maximum value?

Thu, 14 May 2015 08:31:08 GMT

Thanks. I want the over all Total as sum of all months sales. Individual Total on individual month as well.

is it possible.

please see attached

for eg: there is case B01149 with sales in Aug 2015 and Sept 2015. In Aug 2015 the sales is 160 for maximum FirstNumber 3 and in Sept 2015 the sales is 456 for maximum FirstNumber 3. so, I need the overall Total as 616 and individual month Total as 160 for Aug 2015 and 456 for Sept 2015.

would it be possible.

please can help me as this is very priority report to complete.

Re: How to get vaues corresponding to maximum value?

sunny_talwar — Thu, 14 May 2015 10:49:14 GMT

Have a look and see if this is what you want

Best,

Sunny

Re: How to get vaues corresponding to maximum value?

Fri, 15 May 2015 10:45:48 GMT

That's perfect Thanks.

Re: How to get vaues corresponding to maximum value?

sunny_talwar — Fri, 15 May 2015 10:58:39 GMT

No problem

Best,

Sunny