Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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) )
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) )
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...
How do I get the column name?
Like this?
Guys my output should be
SALES 3
62583110
I got help with getting the value now I need the name Sales3
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) )
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) )