Discussion board where members can get started with QlikView.
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
I have tries using pivot and straight etc but nio luck,Please can someone help me with this.?
jagan please help
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]))
Try like this
=Aggr(If(Rank(Sum([Spend])) = 1, [Head Of Service]), [Supplier Name], [Head Of Service]) ---- (Calculated Dimension)
Note: Select Supress Null value option in Dimension tab for Calculated Dimension
Hope this helps you.