topic Re: Compare different fields in QlikView

Compare different fields

sibusiso90 — Thu, 09 Nov 2017 10:52:05 GMT

Hi All

I am trying to get the max of the totals of four columns in QlikView,

E.G

Sales1	Sales2	Sales3	Sales4	Sales5
995319	3757029	5030879	5417965	3910456
242959	5634295	121141	7509077	3608470
7502777	1084741	8225899	9274734	2880707
4170868	8277166	8385531	1208488	8889074
557450	5987442	7259040	9313006	4703888
8675913	8754941	4466229	5742797	2076824
6190032	3326462	1205143	217729	6595792
2995885	774748	5547173	3972913	3414509
4471064	283329	2692634	5043233	1522726
3109227	7931019	2165757	7877809	8894882
5630447	3280298	8312730	4384029	1883162
2439498	6327256	9170954	31844	3587639
46981439	55418726	62583110	59993624	51968129

The last row is the total for the different sales and I want a way to pull the sales 3 as it has the largest Total.
I also want to get the max value out of the five sales.

My Results should be

Sales

62583110

Re: Compare different fields

tresB — Thu, 09 Nov 2017 10:57:23 GMT

For max of totals = RangeMax( Sum(Sales1),Sum(Sales2),Sum(Sales3),Sum(Sales4),Sum(Sales5) )

For max of all = RangeMax( Max(Sales1),Max(Sales2),Max(Sales3),Max(Sales4),Max(Sales5) )

Re: Compare different fields

madhumitha — Thu, 09 Nov 2017 11:18:56 GMT

Hi,

you can use

=NumMax([Sales1],[Sales2],[Sales3],[Sales4],[Sales5])

where [Sales1],[Sales2],[Sales3],[Sales4],[Sales5] are other expressions which would calculate Sum(Sales1) etc...

Re: Compare different fields

sibusiso90 — Thu, 09 Nov 2017 11:35:26 GMT

How do I get the column name?

Re: Compare different fields

prma7799 — Thu, 09 Nov 2017 11:52:52 GMT

Like this?

Re: Compare different fields

sibusiso90 — Thu, 09 Nov 2017 12:21:34 GMT

Guys my output should be

SALES 3

62583110

I got help with getting the value now I need the name Sales3

Re: Compare different fields

tresB — Thu, 09 Nov 2017 12:27:39 GMT

To get name of the field you could try like:

= 'Max of totals is in field : ' &

RangeMaxString( Dual('Sales1' , Sum(Sales1)), Dual('Sales2' , Sum(Sales2)), Dual('Sales3' , Sum(Sales3)), Dual('Sales4' , Sum(Sales4)), Dual('Sales5' , Sum(Sales5)) )

& ' and the value is : '

RangeMax( Sum(Sales1),Sum(Sales2),Sum(Sales3),Sum(Sales4),Sum(Sales5) )

Re: Compare different fields

prma7799 — Thu, 09 Nov 2017 12:33:05 GMT

as tresesco‌ suggest I am doing some changes

= 'Max of totals is in field : ' &

RangeMaxString( Dual('Sales1' , Sum(Sales1)), Dual('Sales2' , Sum(Sales2)), Dual('Sales3' , Sum(Sales3)), Dual('Sales4' , Sum(Sales4)), Dual('Sales5' , Sum(Sales5)) )

& ' and the value is : ' &

RangeMax( Max(Sales1),Max(Sales2),Max(Sales3),Max(Sales4),Max(Sales5) )