cancel
Showing results for 
Search instead for 
Did you mean: 

How to calculate difference in cross tab report

Former Member
0 Kudos
121

Hello All,

I have a cross tab report . I have values for 2 KPI's no of employees joined and no of employees left organization on monthly basis.

I want to calculate employees Joined - employees left organization.

Please let me know how can we achieve this.

PFA the sample report.

Regards

Prasad Ambati

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Prasad,

Please clarify this..how are you identifying the employee information who are left..is there any flag available in your tables..if yes please create two variables..

V1:count(employee) where (employee left='Y') in this i am considering employee left column ia flag which is there in my database

V2:count(employee) where (employee left='N') in this i am considering employee left column ia flag which is there in my database

Now do V2-V1..hope this may help you..

Regards,

Naveen D

Former Member
0 Kudos

Hello Naveen,

We don't have any flag.

Employees Joined and left are the values of KPI's.

Each KPI has some values associated with that for each Month like in Jan 2014 no of joinees are 2000 and no of people left are 1000.

Regards

Prasad Ambati

Former Member
0 Kudos

Hello Prasad,

As per the post..i understood that you have column KPI's which is having the values Employees Joined and left are the values.. now create a variable

1.V1:count(measure) where (KPI='Employees Joined')

2.V2:count(measure) where (KPI='Employees Left')

3.V1-V2

If my understanding is wrong..can you please post some snaps related to your issue..so that i can get some clarity.

Regards,

Naveen D

Former Member
0 Kudos

Hi Prasad,

I think you want the difference between the two columns in the cross tab i.e.

"Count of Joinees" (lets say V1) and "Count of employees left the organisation" (lets say V2)

For achieving this you can do:

1. Create a variable V1 (as measure) for "Count of Joinees" as you have already used in your cross tab (1st Column), just create a variable for it.

2. Create another variable V2 (as measure) for "Count of employees left the organisation" as you have already used in your cross tab (2nd column) just create a variable for it.

3. Add a column next to "SUM" insert the formula =[V1]-[V2] 

I think this should work just try it.

Thanks,

Shardendu Pandey

Former Member
0 Kudos

Hello Shardendu,

Count of Joinees and Count of employees left are the two values of the column KPI_NAME.

The numbers 100,50 etc... are the values form the column MONTH-00.

I tired to create variables but no luck.

Regards

Prasad Ambati

Former Member
0 Kudos

Please clarify what your requirement is in detail.

Former Member
0 Kudos

HI Prasad,

Creat Variable

=[employees Joined] - [employees left organization]

and insert a column on right place the variable in empty column

Add the variable to cross tab

Former Member
0 Kudos

Hello Mahi ,

How can we create =[employees Joined] - [employees left organization] ?

Both are dimensions and we have only one measure called Employee count.

employees joined =500

Employees left=50

Total =500-50=450

The measure column employee count holds 500 and 50 for different dimensions.

Regards

Prasad Ambati

Former Member
0 Kudos

Hello Prasad,

use

=tonumber([employees Joined]) - tonumber([employees left organization] )


OR

=SUM(tonumber([employees Joined])) - SUM(tonumber([employees left organization] ))


Regards,

Mahi

former_member183330
Active Participant
0 Kudos

Hi Mahi,

Create A variable

Var_Diff=([Employee count]Where([EMP Status]="Count of Joinees")) - ([Employee count]Where([EMP Status]="Count of employees left the organization"))

[Employee count] is A measure and [EMP Status] is A dimension where you placed dimension in cross tab upper header. I dont know the dimension name that is the the reason I named it as [EMP Status]. place your dimension name here

Thanks,

G Sampath Kumar

Former Member
0 Kudos

Hello Mahi,

It is not working.

We have the count availble in the measure column Month_00.

No of Employees Joined and Left are Dimension values of the column KPI_NAME.

Regards

Prasad Ambati