Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

vikasmahajan
Honored Contributor III

Calculate CTC Financial Year Wise for Employee

dear All

I have following data for one employee  I want to show CTC of employee  during each financial year 

for eg.   2011 -12  (april 2011 - Mar 2012) is  485784  and in next finyr it is  553824 

wise how  do I calculate please help me.

Following are the salary transaction table every month ctc is updated transaction in table.

CTC_ERNN_EMPLOYEE_IDD_PAY_DATEBASIC_ERNHRA_ERNGROSS_EARN_ERNPF_ERNACESIGROSS_ERN
485784121030/04/201116100805035556193231800
485784121031/05/201116100805031800193231800
485784121030/06/201116100805031800193231800
NULL121031/07/2011NULLNULL53600241237250
553824121031/08/201117100855037250205237250
553824121030/09/201117100855045650205237250
553824121031/10/201117100855037250205237250
553824121030/11/201117100855037250205237250
553824121031/12/201117100855063050205237250
553824121031/01/201217100855037250205237250
553824121029/02/201217100855037250205237250
553824121031/03/201217100855037250205237250

Thanks in advanace

Vikas

4 Replies
sushil353
Honored Contributor II

Re: Calculate CTC Financial Year Wise for Employee

Hi,

Please find attached

According to your data i am assuming that your next financial year starts from 31/08/2011..

Change the "YEARNAME(DATEFIELD,0,OFFSET)" offset value according to your date field and requirement.

HTH

Sushil

vikasmahajan
Honored Contributor III

Re: Calculate CTC Financial Year Wise for Employee

Hi Sushil

Many thanks for reply will work for  if i have  start date April  and end date as  Mar.

Vikas

sujeetsingh
Honored Contributor III

Re: Calculate CTC Financial Year Wise for Employee

SET date from March to jan .

sushil353
Honored Contributor II

Re: Calculate CTC Financial Year Wise for Employee

Hi Vikas,

Try this Code:

Table1:

LOAD

YearName(D_PAY_DATE,0,4) as F_YEAR

,

* Inline [

CTC_ERN,N_EMPLOYEE_ID,D_PAY_DATE,BASIC_ERN,HRA_ERN,GROSS_EARN_ERN,PF_ERN,ACESIGROSS_ERN

485784,          1210,          31/01/2011,          16100,          8050,          35556,          1932,          31800

485784,          1210,          28/02/2011,          16100,          8050,          31800,          1932,          31800

485784,          1210,          31/03/2011,          16100,          8050,          31800,          1932,          31800

NULL,          1210,          30/04/2011,          NULL,          NULL,          53600,          2412,          37250

553824,          1210,          31/05/2011,          17100,          8550,          37250,          2052,          37250

553824,          1210,          30/06/2011,          17100,          8550,          45650,          2052,          37250

553824,          1210,          31/07/2011,          17100,          8550,          37250,          2052,          37250

553824,          1210,          31/08/2011,          17100,          8550,          37250,          2052,          37250

553824,          1210,          30/09/2011,          17100,          8550,          63050,          2052,          37250

553824,          1210,          31/10/2011,          17100,          8550,          37250,          2052,          37250

553824,          1210,          30/11/2011,          17100,          8550,          37250,          2052,          37250

553824,          1210,          31/12/2011,          17100,          8550,          37250,          2052,          37250

553824,          1210,          31/01/2012,          17100,          8550,          63050,          2052,          37250

553824,          1210,          28/02/2012,          17100,          8550,          37250,          2052,          37250

553824,          1210,          31/03/2012,          17100,          8550,          37250,          2052,          37250

];

HTH

Sushil

Community Browser