topic Re: Top 1 SAL in QlikView

Top 1 SAL

qlikviewwizard — Thu, 03 Sep 2015 07:35:42 GMT

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

Re: Top 1 SAL

sunny_talwar — Thu, 03 Sep 2015 07:39:27 GMT

Working with PE of QV. If you can share the source data with expected output, I might be able to help

Re: Top 1 SAL

qlikviewwizard — Thu, 03 Sep 2015 07:54:05 GMT

Hi Sunny,

Please find the data in text file.

Expected result has been shown in post.

Thank you.

Re: Top 1 SAL

sunny_talwar — Thu, 03 Sep 2015 08:15:33 GMT

Your output shows only three jobs what about the about the other two jobs???

Re: Top 1 SAL

qlikviewwizard — Thu, 03 Sep 2015 08:21:23 GMT

Yes,I do provide remaining as well. Thank you.

Re: Top 1 SAL

sunny_talwar — Thu, 03 Sep 2015 08:24:29 GMT

Is this what you are looking for?

Re: Top 1 SAL

sunny_talwar — Thu, 03 Sep 2015 08:28:20 GMT

Updated:

Re: Top 1 SAL

qlikviewwizard — Thu, 03 Sep 2015 08:32:33 GMT

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

Re: Top 1 SAL

sunny_talwar — Thu, 03 Sep 2015 09:55:00 GMT

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?

Re: Top 1 SAL

qlikviewwizard — Thu, 03 Sep 2015 10:15:17 GMT

I want to see the data per LOC,JOB ,YEAR, QUARTER.

Sorry, I did not provide the correct required output data.

Thank you.

Re: Top 1 SAL

jagan — Mon, 07 Sep 2015 01:11:36 GMT

Hi,

Try this expression

Aggr(If(Rank(sum(Sales), 1, 1) = 1, Rank(Sum(Sales),1,1)), JOB, Region, YEAR, QUARTER, EMPNO)

Regards,

Jagan.

Re: Top 1 SAL

Mon, 07 Sep 2015 05:54:19 GMT

Hi,

The easy way to show the top sales is,

goto-> dimension limit tab