Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to show the Top 1 SAL of the Employee details for each JOB and each LOC and for each Year,Quarter as shown below.
Thanks in advance.
EMPNO | ENAME | JOB | MGR | LOC | DNAME | SAL |
7902 | FORD | ANALYST | 7566 | IN | ACCOUNTING | 50400 |
7698 | BLAKE | MANAGER | 7839 | US | RESEARCH | 24200 |
7839 | KING | PRESIDENT | NULL | UK | SALES | 23200 |
Hi,
Try this expression
Aggr(If(Rank(sum(Sales), 1, 1) = 1, Rank(Sum(Sales),1,1)), JOB, Region, YEAR, QUARTER, EMPNO)
Regards,
Jagan.
Working with PE of QV. If you can share the source data with expected output, I might be able to help
Hi Sunny,
Please find the data in text file.
Expected result has been shown in post.
Thank you.
Your output shows only three jobs what about the about the other two jobs???
Yes,I do provide remaining as well. Thank you.
Is this what you are looking for?
Updated:
Hi Sunny,
Something like this.
EMPNO | ENAME | JOB | QUARTER | YEAR | Sales | COMM | Region |
7902 | FORD | ANALYST | Q3 | 2015 | 5120 | 768 | IN |
7876 | ADAMS | CLERK | Q1 | 2015 | 3120 | 468 | US |
7839 | KING | PRESIDENT | Q1 | 2015 | 2970 | 445.5 | UK |
7844 | TURNER | SALESMAN | Q4 | 2015 | 2570 | 385.5 | IN |
7521 | WARD | SALESMAN | Q1 | 2015 | 1720 | 258 | UK |
7369 | SMITH | CLERK | Q1 | 2015 | 920 | 138 | US |
7902 | FORD | ANALYST | Q3 | 2014 | 5120 | 768 | IN |
7876 | ADAMS | CLERK | Q1 | 2014 | 3120 | 468 | US |
7839 | KING | PRESIDENT | Q1 | 2014 | 2970 | 445.5 | UK |
7698 | BLAKE | MANAGER | Q2 | 2014 | 2955 | 443.25 | US |
7844 | TURNER | SALESMAN | Q4 | 2014 | 2570 | 385.5 | IN |
7369 | SMITH | CLERK | Q1 | 2014 | 920 | 138 | US |
You want it for -> Top 1 SAL of the Employee details for each JOB and each LOC and for each Year,Quarter
I still don't see all combinations here. For instance we have just Q3 for Analyst in 2015 in IN. What about Q2, Q1 for Analyst in 2015 in IN? what about other locations?
Can you make sure that you want it for each JOB, LOC, Year and Quarter?
I want to see the data per LOC,JOB ,YEAR, QUARTER.
Sorry, I did not provide the correct required output data.
Thank you.