Find Max value

[#1]

Hi all,

how can i find max value of Head of service for each supplier from the sample data below, please also see the output i am trying to achieve from this data.

All i want is for a supplier what is the max spend under particular head of service  

Supplier Name	Gross Spend	Head Of Service
Supplier 1	100	Service 1
Supplier 1	100	Service 1
Supplier 1	100	Service 1
Supplier 1	100	Service 1
Supplier 1	100	Service 2
Supplier 1	100	Service 2
Supplier 1	100	Service 3
Supplier 1	100	Service 4
Supplier 1	100	Service 5
Supplier 2	400	Service 1
Supplier 2	600	Service 2
Supplier 2	70	Service 2
Supplier 2	800	Service 3
Supplier 3	900	Service 4

Output
Supplier 1	Spend	Head of service
Supplier 1	400	Service 1
Supplier 2	800	Service 3
Supplier 3	900	Service 4

I have tried using pivot and straight tables but no luck.

Does anyone can help me and explain how can I get the output  as above please?

jagan‌  please help

Regards,

Karthik

[tresesco]

Try like

Dimension1: Supplier

Exp1: Max(Aggr(Sum(Spend), Supplier,[Head of Service])

Exp2: FirstSortedValue( [Head of Service], -Aggr(Sum(Spend), Supplier, [Head of Service]))

If you need the column alignment differently, you can drag it accordingly in straight table.

Edit: Corrected

[#3]

Hello Tresesco,

Thanks you very much for your reply.

I have used the expression as you mentioned, i am getting correct value in terms of Head of service. But in terms spend i am getting the total spend instead spend for that head of service.

Sorry for being thick i am new to Qlikview and trying to understand how the expression etc works.

Regards,

Karthik

[Digvijay_Singh]

Try Max(Spend) for second exp..

[Digvijay_Singh]

But your first output entry Supplier 1, 400, Service 1 doesn't exist in data. Might be I misunderstood the requirement.

[Kushal_Chawda]

You can do this from script

Data:

LOAD * Inline [

Supplier Name, Gross Spend, Head Of Service

Supplier 1, 100, Service 1

Supplier 1, 100, Service 1

Supplier 1, 100, Service 1

Supplier 1, 100, Service 1

Supplier 1, 100, Service 2

Supplier 1, 100, Service 2

Supplier 1, 100, Service 3

Supplier 1, 100, Service 4

Supplier 1, 100, Service 5

Supplier 2, 400, Service 1

Supplier 2, 600, Service 2

Supplier 2, 70, Service 2

Supplier 2, 800, Service 3

Supplier 3, 900, Service 4 ];

Left Join

LOAD Distinct [Supplier Name],

[Head Of Service],

sum([Gross Spend]) as Spend_sum

Resident Data

Group by [Supplier Name],[Head Of Service];

Left Join

LOAD Distinct [Supplier Name],

FirstSortedValue([Head Of Service],-Spend_sum) as [Head Of Service],

FirstSortedValue(Spend_sum,-Spend_sum) as Spend_max

Resident Data

Group by [Supplier Name];

Dimension:

[Supplier Name],

[Head Of Service]

Expression:

sum(Spend_max)

[jagan]

Hi Karthik,

Try like this

Dimension:

Supplier Name

=Aggr(If(Rank(Sum([Gross Spend])) = 1, [Head Of Service]), [Supplier Name], [Head Of Service])     ---- (Calculated Dimension)

Expression:

=Sum([Gross Spend])

Select Supress Null value option in Dimension tab for Calculated Dimension

Hope this helps you.

Regards,

Jagan.