Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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)
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.