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 a max value for the below Scenario:

Scenario: All i want to do here under which head of service there is max spend for a supplier from the data set below.

For reference i have also added output i am trying to achieve

  

Supplier NameSpendHead Of Service
Supplier 1200Service 1
Supplier 1300Service 1
Supplier 1500Service 1
Supplier 1600Service 2
Supplier 1600Service 2
Supplier 1600Service 3
Supplier 2500Service 1
Supplier 2600Service 2
Supplier 3600Service 1
Supplier 3600Service 3
Output
Supplier NameHead Of ServiceTotal
Supplier 1Service 21200
Supplier 2Service 2600
Supplier 3Service 3600

I have tries using pivot and straight etc but nio luck,Please can someone help me with this.?

jagan please help

Regards,

Karhik

2 Replies
dclark0699
Contributor

Re: Find max Value

Here's one possible straight table solution

Dimension = Supplier Name

Expression 1 =MAX(AGGR(SUM(Spend),[Supplier Name],[Head Of Service])) //This will give you the highest spend total

Expression 2 =FirstSortedValue(DISTINCT [Head Of Service],-AGGR(SUM(Spend),[Supplier Name],[Head Of Service]))

MVP & Luminary
MVP & Luminary

Re: Find max Value

HI,

Try like this

Dimension:

Supplier Name

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

Expression:

=Sum([Spend])

Note: Select Supress Null value option in Dimension tab for Calculated Dimension

Hope this helps you.

Regards,

Jagan.