Discussion board where members can get started with QlikView.
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
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
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.
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.
Try Max(Spend) for second exp..
But your first output entry Supplier 1, 400, Service 1 doesn't exist in data. Might be I misunderstood the requirement.
You can do this from script
LOAD * Inline [
Supplier Name, Gross Spend, Head Of Service
Supplier 1, 100, Service 1
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 ];
LOAD Distinct [Supplier Name],
[Head Of Service],
sum([Gross Spend]) as Spend_sum
Group by [Supplier Name],[Head Of Service];
FirstSortedValue([Head Of Service],-Spend_sum) as [Head Of Service],
FirstSortedValue(Spend_sum,-Spend_sum) as Spend_max
Group by [Supplier Name];
[Head Of Service]
Try like this
=Aggr(If(Rank(Sum([Gross Spend])) = 1, [Head Of Service]), [Supplier Name], [Head Of Service]) ---- (Calculated Dimension)
Select Supress Null value option in Dimension tab for Calculated Dimension
Hope this helps you.