Change in dimension and calculation of measure

[qlikviewwizard]

Hi All,

I have a report and chart by employee name salaries.

How to track the employee change in designation or promotion based on Hiredate and PromotionDate?

I want to calculate how much Salary he drwan with each designation.

Thanks in advance.

EMP:

LOAD * inline [

EMPNO,ENAME,JOB,MGR,HIREDATE,PRMOTIONDATE,SAL,COMM,DEPTNO

7369,JONES,Jr Engineer,7902,12/17/2000 0:00,8/29/2002 0:00,800,NULL,20

7369,JONES,Engineer,7902,10/23/2003 0:00,8/30/2002 0:00,1600,300,30

7369,JONES,Sr Engineer,7902,5/26/2005 0:00,4/1/2015 0:00,1250,500,30

7369,JONES,MANAGER,7839,4/2/2015 0:00,9/9/9999 0:00,2975,,20

]

[anbu1984]

Can you post expected output?

[qlikviewwizard]

EMPNO	ENAME	JOB	MGR	HIREDATE	PRMOTIONDATE	SAL	COMM	DEPTNO	Expected O/P	Duration in months	Total Earned
7369	JONES	Jr Engineer	7902	12/17/2000 0:00	8/29/2002 0:00	800	NULL	20		20.66666667	16533.33333
7369	JONES	Engineer	7902	10/23/2003 0:00	2/19/2008 0:00	1600	300	30		106	169600
7369	JONES	Sr Engineer	7902	11/15/2010 0:00	4/1/2015 0:00	1250	500	30		94.93333333	118666.6667
7369	JONES	MANAGER	7839	4/2/2015 0:00	9/9/9999 0:00	2975		20		97307.7	289490407.5

[anbu1984]

How did you calculate duration?

[qlikviewwizard]

(PRMOTIONDATE-HIREDATE)/30= no of months

[anbu1984 · Accepted solution]

EMP:

LOad Interval(PRMOTIONDATE-HIREDATE,'dd')/30 As Diff,(Interval(PRMOTIONDATE-HIREDATE,'dd')/30)*SAL ,*;

LOAD EMPNO,ENAME,JOB,MGR,Date#(HIREDATE,'MM/DD/YYYY hh:mm') As HIREDATE,Date#(PRMOTIONDATE,'MM/DD/YYYY hh:mm') As PRMOTIONDATE,SAL,COMM,DEPTNO inline [

EMPNO,ENAME,JOB,MGR,HIREDATE,PRMOTIONDATE,SAL,COMM,DEPTNO

7369,JONES,Jr Engineer,7902,12/17/2000 0:00,8/29/2002 0:00,800,NULL,20

7369,JONES,Engineer,7902,10/23/2003 0:00,2/19/2008 0:00,1600,300,30

7369,JONES,Sr Engineer,7902,5/26/2005 0:00,4/1/2015 0:00,1250,500,30

7369,JONES,MANAGER,7839,4/2/2015 0:00,9/9/9999 0:00,2975,,20

];

[qlikviewwizard]

Thank you so much