Staff Cube with Movement Analysis

[RJ!]

Hi All,

I'm trying to build a Cube to store our Staff HC data and had this great idea of also including "dummy" accounts so that we could have a hierarchy like this:

• Beginning of Period (BOP)
• New Staff
• Attrition
• Transfers in
• Transfers out
• End of Period (EOP)

As you can see in the attched file I'm having issues with the Cube rules to make the above hierachy work, specifically I'm not sure how to get TM1 to differentiate between a New Staff Member & a Transfer In from another Responsibility centre. Since the Cube defaults "Blank" to Zero (0) it makes it impossible (it seems) to use the DB() formula to solve the problem. Has anyone else tried anything similar in their environment?

Below are the current Rules / Feeders for the Cube:
(Note the New & Transfers In + Attrition & Transfers out have the same code at present)

Code: Select all

# 221012 - JC Rules to Feed the Account Structure

Skipcheck ;

# New	
['950191BA']  = N: If ( DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'9905500000-D909001',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 0 & 
                                    DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'9905500000-D909001',!Period,'Balance') = 1,
                                       If (DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950191BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 1,0,1),0) ;

# Attrition	
['950192BA'] = if ( DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'9905500000-D909001',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 1 & 
            	         DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'9905500000-D909001',attrs('Period', !Period, 'Next_Mth'),!MeasureType) = 0, 
	            If (DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950192BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 1,0,1),0) ;

#Transfer In
['950193BA'] = if ( DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'9905500000-D909001',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 0 & 
            	         DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'9905500000-D909001',!Period,!MeasureType) = 1, 
	            If (DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950193BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 1,0,1),0) ;
	
# Transfer Out			
['950194BA'] = if ( DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'9905500000-D909001',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 1 & 
            	         DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'9905500000-D909001',attrs('Period', !Period, 'Next_Mth'),!MeasureType) = 0, 
	            If (DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950194BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 1,0,1),0) ;

# EOP	
['950195BA'] = N: if ( DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'940198PA',!Period,'Balance') = 1, 1, 0 )  ;

# BOP
#'950195BA'] = if ( DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'940198PA',!Period,'Balance') = 1, 1, 0 )  ;

Feeders;

['940198PA'] => ['950191BA'] ;

[winsonlee]

How does your raw data looks like ? what is the trigger point that determine that (AA12345) is a new staff or transferred staff ? I presume you need to have such data exist somewhere before you are able to tell the rules what to do.

[RJ!]

Hi Winson,

The Raw Data is a Monthly Flat File that basically lists out all of the Staff ID's & names with their Cost Centre and a few other roll up details (for that Month only).

So to determine if the Staff are "New" we have tried to apply the logic that if their ID did not exist last month, then they must be new.
If the ID existed last month, but not this month, it's Attrition.
For the Transfers in & Transfers out, the logic "should be" if the Cost Centre is different between the 2 months, then the staff have transfered between departments & depending if it's a positive or negative will determine if it's a transfer in or out.

I'm not sure how to draft some logic that can help with determining the difference between Transfered out & Attrition nor Transfer in & New.

I'm sure that this can be done via a TI process but would prefer to have it as a rule if possible.