How to calculate difference in cross tab report

[Former Member]

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

[Former Member]

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]

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]

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]

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]

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]

Please clarify what your requirement is in detail.

[Former Member]

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]

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]

Hello Prasad,

use

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

OR

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

Regards,

Mahi

[former_member183330]

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]

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