Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Find Max value

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 NameGross SpendHead Of Service
Supplier 1100Service 1
Supplier 1100Service 1
Supplier 1100Service 1
Supplier 1100Service 1
Supplier 1100Service 2
Supplier 1100Service 2
Supplier 1100Service 3
Supplier 1100Service 4
Supplier 1100Service 5
Supplier 2400Service 1
Supplier 2600Service 2
Supplier 270Service 2
Supplier 2800Service 3
Supplier 3900Service 4

   

Output
Supplier 1SpendHead of service
Supplier 1400Service 1
Supplier 2800Service 3
Supplier 3900Service 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

6 Replies
MVP
MVP

Re: Find Max value

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

Not applicable

Re: Find Max value

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
Honored Contributor III

Re: Find Max value

Try Max(Spend) for second exp..

188.PNG

Digvijay_Singh
Honored Contributor III

Re: Find Max value

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

Re: Find Max value

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)

MVP & Luminary
MVP & Luminary

Re: Find Max value

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.