Hello everyone,
Appreciate any help with the below requirement.
I am trying to create a calculation where the first part of the set is in the context of the dimension in the chart while the second needs to exclude the dimension in the chart.
From the below sample file, for each Clerk, I want take the clients and rank all the Customer with a relation to those clients.
For Ex: For Clerk = Adil, Customer2 needs to be ranked against all other customers who have a relationship with Client Sri
For Clerk Steve, Customer 1 needs to be ranked against all other customers who have relationships with Clients Arthur & Sri
My input file is below
| Client | Customer | Clerk | Value |
| Arthur | Customer7 | - | 2844558675 |
| Arthur | Customer6 | - | 926561025 |
| Arthur | Customer1 | Steve | 2844558675 |
| Arthur | Customer3 | Matthew Todd Ashworth | 2405949750 |
| Arthur | Customer4 | John | 2405949750 |
| Arthur | Customer5 | Stanford | 2373053625 |
| Arthur | Customer8 | Morgan | 2552151375 |
| Arthur | Customer9 | Maria | 2243299050 |
| Sri | Customer1 | Steve | 2270423012 |
| Sri | Customer2 | Adil | 681126420 |
| Sri | Customer3 | Brian | 1362252840 |
| Sri | Customer5 | George | 2270423012 |
| Sri | Customer6 | James | 2043380872 |
| Sri | Customer7 | Todd | 1589294980 |
| Sri | Customer8 | Morgan | 2043380872 |
| Sri | Customer9 | Maria | 1816338732 |
The Output table in qlikview should be as below
| Clerk | count(Client) | sum(Value) | Rank |
| Adil | 1 | 681126420 | 8 |
| Brian | 1 | 1362252840 | 7 |
| George | 1 | 2270423012 | 1 |
| James | 1 | 2043380872 | 3 |
| John | 1 | 2405949750 | 4 |
| Maria | 2 | 4059637782 | 5 |
| Matthew Todd Ashworth | 1 | 2405949750 | 4 |
| Morgan | 2 | 4595532247 | 3 |
| Stanford | 1 | 2373053625 | 6 |
| Steve | 2 | 5114981687 | 1 |
| Todd | 1 | 1589294980 | 6 |
Thanks,
Sri
