cancel
Showing results for 
Search instead for 
Did you mean: 

Story Reports - requirement to calculate movement in and out of organization

sclow
Explorer
0 Kudos

Hi

I need advice on Story Reports.

I have a requirement to calculate by Employee Central event reasons out of a organization and as well as into the organization.

And it is to calculate the total movement in/ out across a selected period eg 12 month period.

How can we achieve this?

E.g i select a Division A, i need to calculate all event reasons into Div A and at the same time calculate all event reasons that resulted in a move to a position not in the Div A (ie outside)

How can this be achieved when story report applies the filter based on selected Division A?

I can do a calculated column to count the event reasons in the Div but how can i do the calculation for "outside" of that Div?

Also the story report filter can be division, department, business unit.

How can the calculation be dynamic based on the selection filter/ criteria?

Thanks & regards

 

View Entire Topic
ErinRoberts
Product and Topic Expert
Product and Topic Expert

Hello,

Unfortunately, it is not yet possible in Story to use a filter selection within a calculation.

Below is details on something I just tried to see if it can help you get close to what you need.

Maybe you already figured this part out, but I think you will need to start your data model with the Person schema as you will likely need to duplicate the Job Information table in order to get the previous (moved out from) Division.  Think change reporting if you are familiar with it.

The first version of Job Information should be set to a date range 12 months.

The second version of Job Information should not have any time filter so that it pulls all records.

You will need a calculated column to find the number of days between the start date from the first job information table and the end date from the second job information table.

In attempting this I also created calculations as follows:

Moves Within: IF Division = Previous Division, 1, 0

Moves In/Out: If Division != Previous Division, 1, 0

In your data model, you will need the following filters:

Your days between calculation mentioned above = 1, this is to identify just the previous job information records

You will probably also need to filter on specific movement events so as not to count other generic data change events as movements within a Division.

Then on the Story I created a simple cross tab with Division on the Rows (my screenshot shows Dept because I didn't have any Division changes in my instance) and my two calculated columns as the Columns.

ErinRoberts_0-1707754884023.png

I added a Story filter on Department (again because I only have Department changes, and no Division changes).  When I then filter, I can see the number of moves into and moves within the department. 

ErinRoberts_1-1707754960877.png

I think the part I'm still missing for you is the number of moves out of the Division.

Right now, my only thought is a separate cross tab (I understand this is not ideal), this time showing the Previous Department in the Rows and using the same measure for moves out of/into the department.  But the filtering doesn't work well for the second table.  Is there a strong reason you need to be able to filter to the Division?

I even played around with adding the previous department and department as second rows on each of the two cross tabs respectively to see where the moves came from/went to.

ErinRoberts_2-1707755973761.png

I hope this may help a little in getting you close to what you need.  If you have WFA there is a WFA metrics pack that provide this information.

 

 

sclow
Explorer
0 Kudos

Hi Erin

Thank you for your response.

I understand that i must use the so-called "change management" logic whereby i have to create a copy of Job Info and then using a calculated column to compare the end date vs the start date of the 2 Job Info records to detect if a change is valid or not

The requirement i have is calculating each event reason the movement in and out.

Thus i will then need to start creating calculated columns for each event reason and comparing prev vs current division if there was a change(based on the date logic above).

I understand about creating a cross-tab to list out the movement in and out in a table format.

However is it possible to not present in table format but display the count as a chart instead? So its 1 numeric point for "in" and 1 numeric point for "out" ?

My understanding is that creating these calculated columns at the query level will not work because the formula at the query level cannot do that comparison as it needs to look at a others records in order to do the comparison....but i am wondering if there is any way of do that sort of comparison in the query itself.

Regarding your question "Is there a strong reason you need to be able to filter to the Division?"

The requirement from the client was they they want to be able to filter based on a few things - division, business unit, department , sections and upto team level

Which is why i was asking if the calculation logic for the previous vs current can be dynamic.

I guess the answer is no - since i have to create logic for check for each of these filter columns.

Thanks

regards