TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Data Not Moving to Cell

https://www.tm1forum.com/viewtopic.php?t=15378

Page 1 of 1

Data Not Moving to Cell

Posted: Tue Jun 23, 2020 6:57 pm
by bbrady74
Good afternoon everyone,

This is my first time posting to this forum, so I apologize in advance if I'm not doing something properly. Please feel free to correct anything I'm not doing as I should. I'm a relatively new user to TM1 and learning each day, but obviously not an expert in any way.

The Problem:
I cannot seem to get the wages line to populate (yellow highlight) as can be seen in attachment "Wages Screen". Other lines such as Adjusted Wage Calc are populating, so I don't believe the issue is Dimension or Dimension Attribute related. I have attached files showing the Dec 2021 & Jan 2022 "Trace Calc" results for account "60810110 - Wages". Also, the Calendar Process for both Dec 2021 and Jan 2022 are set to "Y".

I appreciate your assistance helping me solve this issue. If more information is needed, please let me know, I just didn't want to post too much or unnecessary detail.

Thank you,

BB

Re: Data Not Moving to Cell

Posted: Tue Jun 23, 2020 7:31 pm
by lotsaram
The issue is not with the calculation but with the feeding. The screenshots of the rule trace don't help at all as due to the Continue the tracer only shows the FIRST rule which applies to the cell and not the FINAL AND ACTUAL calculation. But in this case it doesn't matter as the screenshot of the cube view gives the answer.

You can see that the leaf intersection of Adjusted Wage Calc / Jan 2022 contains a value. This tells us the N: rule to do the calculation is working. But the consolidation 60810110 is 0. Unless there is a C: rule specifying this it can mean only one thing. The leaf cell Adjusted Wage Calc / Jan 2022 is not fed.

All N: rules require feeders. Add the feeder and the calculations will sum up as the dimensions consolidate.

Re: Data Not Moving to Cell

Posted: Tue Jun 23, 2020 7:45 pm
by bbrady74
Thank you for the reply. Follow-up if you please. Why does the consolidation work for Dec 2021 and stop working for Jan 2022? If the Dec 2021 is working, shouldn't the Jan 2022 continue to be viable?

Thank you,
BB

Re: Data Not Moving to Cell

Posted: Tue Jun 23, 2020 7:54 pm
by Wim Gielis
bbrady74 wrote: ↑Tue Jun 23, 2020 7:45 pm Why does the consolidation work for Dec 2021 and stop working for Jan 2022? If the Dec 2021 is working, shouldn't the Jan 2022 continue to be viable?
No, these periods are not linked. As said, there is an issue at the level of the feeder (they are wrong or just left out where they should not be left out).

Re: Data Not Moving to Cell

Posted: Tue Jun 23, 2020 8:08 pm
by bbrady74
Wim Gielis wrote: ↑Tue Jun 23, 2020 7:54 pm
bbrady74 wrote: ↑Tue Jun 23, 2020 7:45 pm Why does the consolidation work for Dec 2021 and stop working for Jan 2022? If the Dec 2021 is working, shouldn't the Jan 2022 continue to be viable?
No, these periods are not linked. As said, there is an issue at the level of the feeder (they are wrong or just left out where they should not be left out).
Thank you. So to correct the Feeder, is that in the Business Rules? Also, is there a certain bit of code to look for? I have searched the Business Rules for C: & N: but didn't see anything that looked correct? I could attach a word document of the Business Rules if that is better?

Sincerly,

BB

Re: Data Not Moving to Cell

Posted: Tue Jun 23, 2020 8:10 pm
by Wim Gielis
You can copy/paste the relevant rules and feeders here.

Re: Data Not Moving to Cell

Posted: Tue Jun 23, 2020 8:18 pm
by bbrady74
These are all of the Business Rules and Feeders associated with this model.

Thank you very much for reviewing!
BB

Code: Select all

#Region System
FEEDSTRINGS;
SKIPCHECK;
#EndRegion

#DB('Payroll Model', !Measure, !Version, !time_mqy, !regioncc, !cc_emp, !Accounts)

#Region Calendar and Version Processing

['prior forecast'] = STET;

######This rule is used to limit processing in out years####
[] = If( DB('Calendar Process', !time_mqy, 'Process YN') @='N', STET, continue);

['Forecast', 'Amount'] = N:
  IF( DB('Calendar Process', !time_mqy, 'Calendar Verison') @= 'Actual' ,
  ['Actual'], CONTINUE
) ;



['Input'] = N: STET;
#['Actual'] = N: STET;

['Forecast', { 'Start Date', 'Term Date', 'Status','comment','Vehicle need'}] = STET;




[ 'Forecast' , 'Time_MQY':'comment', 'Annual Salary'] = N:
  IF( SUBST(!cc_emp, 1, 3) @='New' , STET, CONTINUE );

[ 'Forecast' , 'Time_MQY':'comment', {'Position Title','Full or Part Time',
  'Start Date','Comment','Bonus Type','Exempt/Non Exempt'}] = S:
  IF( SUBST(!cc_emp, 1, 3) @='New' , STET, CONTINUE );

[ 'Forecast' , 'Time_MQY':'comment', 'Tax State' ] = S:
  IF( SUBST(!cc_emp, 1, 3) @='New' , STET,
     DB( 'Payroll Model', !Measure, 'Actual', 'comment', !regioncc, !cc_emp, 'Tax State' ) );

[ 'Forecast' , 'Time_MQY':'comment' ] = N:
  DB( 'Payroll Model', !Measure, 'Actual', 'comment', !regioncc, !cc_emp, !Accounts ) ;

[ 'Forecast' , 'Time_MQY':'comment'] = S:
  DB( 'Payroll Model', !Measure, 'Actual', 'comment', !regioncc, !cc_emp, !Accounts ) ;
#EndRegion


#Region- Calculate Headcount

['Forecast', 'Count'] = N:
IF( DB( 'Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp, 'Annual Salary') = 0 , STET ,
  CONTINUE);

#Region- New Headcount
['Forecast', 'Count'] = N:
IF( (SUBST( !cc_emp, 1, 3 ) @= 'New' &
     DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,
	'Start Date' ) @= '' ), STET ,
   IF((SUBST( !cc_emp, 1, 3 ) @= 'New' &
    DAYNO( SUBST( !Time_MQY, 7, 4 )
    | '-' | SUBST( !Time_MQY, 1, 2 )
    | '-' | SUBST( !Time_MQY, 4, 2 ) ) >=
     (ATTRN( 'Time_MQY',DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,
	'Start Date' ), 'SerialDate' ) )
    ),
  DB('Payroll Model', 'Amount', 'Forecast', !time_mqy, !regioncc, !cc_emp, 'CurOpenNew_Load') ,
CONTINUE)
);

#EndRegion

#Region- Existing Headcount
####  clm
['Forecast', 'Count'] = N:
IF (ATTRN('Time_MQY', !Time_MQY , 'SerialDate')> 0 & SUBST( !cc_emp, 1, 3 ) @<> 'New' ,
  IF( DB( 'Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp, 'Term Date') @= '',
      DB('Payroll Model', 'Amount', 'Forecast', !time_mqy, !regioncc, !cc_emp, 'CurOpenNew_Load') ,
      IF(( ATTRN( 'Time_MQY', DB( 'Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,
	 'Term Date'), 'SerialDate' )
     	>= DAYNO( SUBST( !Time_MQY, 7, 4 ) | '-' | SUBST( !Time_MQY, 1, 2 ) | '-' |
                    SUBST( !Time_MQY, 4, 2 )
                    )
                ) %
               ( DB( 'Payroll Model', !Measure, !Version, 'comment', !regioncc,
               !cc_emp, 'Term Date') @= ''),
              DB('Payroll Model', 'Amount', 'Forecast', !time_mqy, !regioncc, !cc_emp, 'CurOpenNew_Load'),
         STET )
      ),
  STET)
;

[{'Full Time Headcount' , 'Part Time Headcount'}]= N: IF(
  DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,'Position Title')@='Intern',
  STET,
 CONTINUE);

['Full Time Headcount'] = N: IF(
  DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,'Full or Part Time')@='F',
  ['count'],
 0);

['Part Time Headcount'] = N: IF(
  DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,'Full or Part Time')@='P',
  ['count']*.5,
 0);



#EndRegion
#EndRegion

#Region Base, Daily, Salary Calc

##Use this to calculate the current salary including the hourly from the prior salary
['Forecast','Base Salary'] = N:
  IF( ['Count']= 1 , CONTINUE, STET );

###############################################################
### Actual version of base salary, Current Daily Rate, and Adjusted Daily rate
### calculated upon TI process from Nilda's file
###############################################################

['Overtime %'] = N: IF(
  DB('Payroll Model', 'Amount', 'Forecast','comment', !regioncc, !cc_emp,'Exempt/Non Exempt')@='N',
  .015,
  0);

[{'60810400', '60810200','H_Base Salary Days in Payroll',
'Current Payroll Daily Rate','Adjusted Payroll Daily Rate','H_New Salary Days in Payroll'}, 'Forecast', 'Amount'] = N:
  IF(['Count'] = 0 , STET, CONTINUE) ;

['Forecast','Base Salary'] = N:IF( SUBST( !cc_emp, 1, 3 ) @= 'New' &
    DAYNO( SUBST( !Time_MQY, 7, 4 )
    | '-' | SUBST( !Time_MQY, 1, 2 )
    | '-' | SUBST( !Time_MQY, 4, 2 ) ) =
     ATTRN( 'Time_MQY',DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,
	'Start Date' ), 'SerialDate' ),
   (DB('Payroll Model', 'Amount', 'Forecast','comment', !regioncc, !cc_emp, 'Annual Salary')\
    ATTRN('Time_MQY', 'Total FY - '|attrs('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY') , 'workdays')) *
    ATTRN('Time_MQY', !Time_MQY , 'workdays'),
    IF( ['Actual'] <> 0 , ['Actual'], CONTINUE )
);

['Forecast','Current Daily Rate'] = N:
IF (ATTRN('Time_MQY', !Time_MQY , 'SerialDate')> 0 ,
  DB('Payroll Model', 'Amount', !Version, !Time_MQY, !regioncc, !cc_emp,'Base Salary')\
    ATTRN('Time_MQY', !Time_MQY , 'workdays'),
0);

['Forecast','Adjusted Daily Rate' ] = N:IF (ATTRN('Time_MQY', !Time_MQY , 'SerialDate')> 0 ,
  ['Current Daily Rate'] + (['Current Daily Rate']*['Merit %']),
0);

['Forecast','Base Salary'] = N:
  DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ), !regioncc, !cc_emp,
     'Adjusted Daily Rate'  ) *
      ATTRN('Time_MQY', !Time_MQY, 'workdays') ;

['Merit $'] = N: ['Base Salary'] * ['Merit %'];

['OT $'] = N: ['Base Salary'] * ['Overtime %'];

['Forecast','Adjusted Wage Calc'] = N:
(['Adjusted Daily Rate'] + (['Adjusted Daily Rate']*['Overtime %']))
     * ATTRN('Time_MQY' , !Time_MQY , 'workdays');

['Forecast','H_Base Salary Days in Payroll'] = N:IF( SUBST( !cc_emp, 1, 3 ) @= 'New' &
    DAYNO( SUBST( !Time_MQY, 7, 4 )
    | '-' | SUBST( !Time_MQY, 1, 2 )
    | '-' | SUBST( !Time_MQY, 4, 2 ) ) =
     ATTRN( 'Time_MQY',DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,
	'Start Date' ), 'SerialDate' ),
   (DB('Payroll Model', 'Amount', 'Forecast','comment', !regioncc, !cc_emp, 'Annual Salary')\
    ATTRN('Time_MQY', 'Total FY - '|attrs('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY') , 'payrolldays')) *
    ATTRN('Time_MQY', !Time_MQY , 'payrolldays'),
    IF( ['Actual'] <> 0 , ['Actual'], CONTINUE )
);
### Check this. It may have been taken care of aleady.  If not, this needs to shut off if the individual has a term date.
['Forecast','Current Payroll Daily Rate'] = N:
IF (ATTRN('Time_MQY', !Time_MQY , 'SerialDate')> 0 ,
  DB('Payroll Model', 'Amount', !Version, !Time_MQY, !regioncc, !cc_emp,'H_Base Salary Days in Payroll')\
    ATTRN('Time_MQY', !Time_MQY , 'payrolldays'),
0);

['Forecast','Adjusted Payroll Daily Rate' ] = N:IF (ATTRN('Time_MQY', !Time_MQY , 'SerialDate')> 0 ,
  ['Current Payroll Daily Rate'] + (['Current Payroll Daily Rate']*['Merit %']),
0);

['Forecast','H_Base Salary Days in Payroll'] = N:
  DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ), !regioncc, !cc_emp,
     'Adjusted Payroll Daily Rate'  ) *
      ATTRN('Time_MQY', !Time_MQY, 'payrolldays') ;

['Forecast','H_New Salary Days in Payroll'] = N:
(['Adjusted Payroll Daily Rate'] + (['Adjusted Payroll Daily Rate']*['Overtime %']))
     * ATTRN('Time_MQY' , !Time_MQY , 'payrolldays');

#EndRegion end of salary rules


#Region Bonus Calc

['Forecast','Bonus %'] = N: IF( (SUBST( !cc_emp, 1, 3 ) @= 'New' &
     DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,
	'Start Date' ) @= '' ), STET ,
   IF((SUBST( !cc_emp, 1, 3 ) @= 'New' &
    DAYNO( SUBST( !Time_MQY, 7, 4 )
    | '-' | SUBST( !Time_MQY, 1, 2 )
    | '-' | SUBST( !Time_MQY, 4, 2 ) ) >=
     (ATTRN( 'Time_MQY',DB('Payroll Model', !Measure, !Version, 'comment', !regioncc, !cc_emp,
	'Start Date' ), 'SerialDate' ) )
    ),
  ATTRN('Bonus Type',
   DB('Payroll Model', 'Amount', !Version, 'comment', !regioncc, !cc_emp, 'Bonus Type'),'BonusPctg'),
  ['count']* ATTRN('Bonus Type',
   DB('Payroll Model', 'Amount', !Version, 'comment', !regioncc, !cc_emp, 'Bonus Type'),'BonusPctg')
));


['Forecast', '60810400'] = N:
IF(   ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm') @= 'Marketing Underwriter'%
	ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm') @= 'Director Underwriting' %
	ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm') @= 'Commercial Lines Underwriter',
  ( ['Bonus %']*['Adjusted Wage Calc']) , 0);

#['H_Bonus Key']= S: IF(
#DB('Payroll Model', 'Amount', !Version, 'comment', !regioncc, !cc_emp, 'Bonus Type')@='MU','1',
#  IF(DB('Payroll Model', 'Amount', !Version, 'comment', !regioncc, !cc_emp, 'Bonus Type')@='11', '11',
#  IF(DB('Payroll Model', 'Amount', !Version, 'comment', !regioncc, !cc_emp, 'Bonus Type')@='12', '12', '')
#  )
#);

#['H_Bonus Key']= S: IF(
#DB('Payroll Model', 'Amount', !Version, 'comment', !regioncc, !cc_emp, 'Bonus Type')@='1','1', STET);

### If bonus type is not null and position is not an MU the populate with 2%

['Key Contributor %','Forecast'] = N: IF (
  DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type') @<> '' &
  ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm')@<> 'Marketing Underwriter',
  .02 ,0 ) ;


['Key Contributor Amount','Forecast']  = N: IF(
  ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm') @='Administrative' %
  ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm')@='Director' %
ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm')@='Managing Director' %
  ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm')@='Manager' %
  ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm')@='Professional' %
ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm')@='PTP 7%' %
ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm')@='PTP 9%' %
ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm')@='Supervisor' ,
  (['Adjusted Wage Calc']*['Key Contributor %'])+['Key Contributor Additional Items'], 0 )
;


['60810200','Forecast']  = N: IF(
  ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm') @= 'Marketing Underwriter'%
	ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm') @= 'Director Underwriting' %
	ATTRS('Bonus Type',
      DB('Payroll Model', 'Amount', !Version,'comment', !regioncc, !cc_emp, 'Bonus Type')
     , 'BonusTypeNm') @= 'Commercial Lines Underwriter',
STET,
['H_New Salary Days in Payroll']*['Bonus %'] );

#Endregion


['Cum Comp'] = N:
IF( ATTRN('Time_MQY' , !Time_MQY , 'MonthNo' ) = 1 ,
    ['Total Compensation'],
     DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ),
   !regioncc, !cc_emp, 'Cum Comp') + ['Total Compensation']
 );

#Region FICA Calc
['FICA Rate', 'Forecast'] = N: DB('Tax Model', !Version,
      DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FICA Rate')
;

['FICA Amount', 'Forecast'] = N:
IF((['Cum Comp'] >=
   DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY') , 'FICA Ceiling') ) &
   (ATTRN('Time_MQY' , !Time_MQY , 'MonthNo' ) = 1) ,
    DB('Tax Model', !Version,
        DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FICA Ceiling')
 * ['FICA Rate'] ,
   IF(ATTRN('Time_MQY' , !Time_MQY , 'MonthNo' ) = 1 ,
       ['Cum Comp'] * ['FICA Rate'],
      IF( DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ),
         !regioncc, !cc_emp, 'Cum Comp') >=
          DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
              ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FICA Ceiling') ,
          STET,
         IF( ['Cum Comp'] >=
          DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
              ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FICA Ceiling'),
             (DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
                 ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FICA Ceiling') -
              DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ),
             !regioncc, !cc_emp, 'Cum Comp')  ) *
              ['FICA Rate'] ,
           ['Total Compensation'] * ['FICA Rate']
           )
        )
    )
);
#EndRegion
#Region FMHI

['FMHI Rate', 'Forecast'] = N: DB('Tax Model', !Version,
      DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FMHI Rate')
;

['Forecast', 'FMHI Amount'] = N: (['Total Compensation']*['FMHI Rate']);
#EndRegion

#Region FUTA

['FUTA Rate', 'Forecast'] = N: DB('Tax Model', !Version,
      DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FUTA Rate')
;

['60820500', 'Forecast'] = N:
IF((['Cum Comp'] >=
   DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY') , 'FUTA Ceiling') ) &
   (ATTRN('Time_MQY' , !Time_MQY , 'MonthNo' ) = 1) ,
    DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FUTA Ceiling')
 * ['FUTA Rate'] ,
   IF(ATTRN('Time_MQY' , !Time_MQY , 'MonthNo' ) = 1 ,
       ['Cum Comp'] * ['FUTA Rate'],
      IF( DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ),
         !regioncc, !cc_emp, 'Cum Comp') >=
          DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
              ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FUTA Ceiling') ,
          STET,
         IF( ['Cum Comp'] >=
          DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
              ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FUTA Ceiling'),
             (DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
                 ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'FUTA Ceiling') -
              DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ),
             !regioncc, !cc_emp, 'Cum Comp')  ) *
              ['FUTA Rate'] ,
           ['Total Compensation'] * ['FUTA Rate']
           )
        )
    )
);
#EndRegion
#Region SUTA

['SUTA Rate', 'Forecast'] = N: DB('Tax Model', !Version,
      DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'SUTA Rate')
;

['60820700', 'Forecast'] = N:
IF((['Cum Comp'] >=
   DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY') , 'SUTA Ceiling') ) &
   (ATTRN('Time_MQY' , !Time_MQY , 'MonthNo' ) = 1) ,
    DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
      ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'SUTA Ceiling')
 * ['SUTA Rate'] ,
   IF(ATTRN('Time_MQY' , !Time_MQY , 'MonthNo' ) = 1 ,
       ['Cum Comp'] * ['SUTA Rate'],
      IF( DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ),
         !regioncc, !cc_emp, 'Cum Comp') >=
          DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
              ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'SUTA Ceiling') ,
          STET,
         IF( ['Cum Comp'] >=
          DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
              ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'SUTA Ceiling'),
             (DB('Tax Model', !Version, DB('Payroll Model', 'Amount' , 'Forecast', 'comment', !Regioncc, !cc_emp , 'Tax State'),
                 ATTRS('Time_MQY',!Time_MQY,'Fiscal_Year_YYYY'), 'SUTA Ceiling') -
              DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_Prior' ),
             !regioncc, !cc_emp, 'Cum Comp')  ) *
              ['SUTA Rate'] ,
           ['Total Compensation'] * ['SUTA Rate']
           )
        )
    )
);
#EndRegion


#Region Benefits
['Forecast',{'60940100','60940200','60940300','60940400','60940500','60920600','60960100'}] = N:
IF(['Adjusted Wage Calc'] = 0,STET,CONTINUE);

['60920600'] = N: DB('Benefits', 'Forecast', !time_mqy, 'Total Rewards Calc')*['Count'];

['60940100'] = N: DB('Benefits', 'Forecast', !time_mqy, 'Health Ins Amount')*['Count'];

['60940200'] = N: DB('Benefits', 'Forecast', !time_mqy, 'Dental Ins Amount')*['Count'];

['60940300'] = N:
IF ((['Adjusted Wage Calc']*12*2)>700000 , (700000/1000)*
  DB('Benefits', 'Forecast', !time_mqy, 'Group Term Life Insurance Rate') ,
 (['Adjusted Wage Calc']*12*2/1000)*
  DB('Benefits', 'Forecast', !time_mqy, 'Group Term Life Insurance Rate')
);

['60940400'] = N: ['Count'] * DB('Benefits', 'Forecast', !time_mqy, 'Short Term Dis Amount') +
IF (['Adjusted Wage Calc']>23333, (23333/1000)* DB('Benefits', 'Forecast', !time_mqy, 'Long Term Disability Rate'),
    (['Adjusted Wage Calc']/1000)*DB('Benefits', 'Forecast', !time_mqy, 'Long Term Disability Rate')
);

['60940500'] = N: ['Total Compensation'] * DB('Benefits', 'Forecast', !time_mqy, 'Workers Comp %');

['60960100'] = N: (['Adjusted Wage Calc']+['Total Bonuses']+['Lump Sum Amount'])*
  DB('Benefits', 'Forecast', !time_mqy, 'Pension %');

#EndRegion



#60940100-Health Insurance
#60940200-Dental Insurance
#60940300-Life Insurance
#60940400-Disability Insurance
#60940500-Workers Compensation Ins
#60960100-Profit Sharing
#60920600-Total Rewards Recognition

#Region C-Calculation rules: Dimension:Accounts
#Autogenerated CALC CONSOLIDATED 4163636F756E7473
   #Region Dimension:Accounts, Member:Total - Headcount
   #Autogenerated MEMBERCALC CONSOLIDATED 5B4163636F756E74735D2E5B546F74616C202D2048656164636F756E745D
   #@AutoGenerated
   ['Accounts':{'Total - Headcount'}]=C:IF(DTYPE('time_mqy', !time_mqy)@='C',
   IF(DIMIX('time_mqy', ATTRS('time_mqy', !time_mqy, 'lastperiod'))>0,
   DB('Payroll Model', !Measure, !Version, ATTRS('time_mqy', !time_mqy, 'lastperiod'), !regioncc, !cc_emp, 'Total - Headcount'),
   DB('Payroll Model', !Measure, !Version, ELCOMP('time_mqy', !time_mqy, ELCOMPN('time_mqy', !time_mqy)), !regioncc, !cc_emp, 'Total - Headcount')),
   CONTINUE);
   #EndRegion
   #Region Dimension:Accounts, Member:Full Time Headcount
   #Autogenerated MEMBERCALC CONSOLIDATED 5B4163636F756E74735D2E5B46756C6C2054696D652048656164636F756E745D
   #@AutoGenerated
   ['Accounts':{'Full Time Headcount'}]=C:IF(DTYPE('time_mqy', !time_mqy)@='C',
   IF(DIMIX('time_mqy', ATTRS('time_mqy', !time_mqy, 'lastperiod'))>0,
   DB('Payroll Model', !Measure, !Version, ATTRS('time_mqy', !time_mqy, 'lastperiod'), !regioncc, !cc_emp, 'Full Time Headcount'),
   DB('Payroll Model', !Measure, !Version, ELCOMP('time_mqy', !time_mqy, ELCOMPN('time_mqy', !time_mqy)), !regioncc, !cc_emp, 'Full Time Headcount')),
   CONTINUE);
   #EndRegion
   #Region Dimension:Accounts, Member:Part Time Headcount
   #Autogenerated MEMBERCALC CONSOLIDATED 5B4163636F756E74735D2E5B506172742054696D652048656164636F756E745D
   #@AutoGenerated
   ['Accounts':{'Part Time Headcount'}]=C:IF(DTYPE('time_mqy', !time_mqy)@='C',
   IF(DIMIX('time_mqy', ATTRS('time_mqy', !time_mqy, 'lastperiod'))>0,
   DB('Payroll Model', !Measure, !Version, ATTRS('time_mqy', !time_mqy, 'lastperiod'), !regioncc, !cc_emp, 'Part Time Headcount'),
   DB('Payroll Model', !Measure, !Version, ELCOMP('time_mqy', !time_mqy, ELCOMPN('time_mqy', !time_mqy)), !regioncc, !cc_emp, 'Part Time Headcount')),
   CONTINUE);
   #EndRegion
   #Region Dimension:Accounts, Member:LOA Headcount
   #Autogenerated MEMBERCALC CONSOLIDATED 5B4163636F756E74735D2E5B4C4F412048656164636F756E745D
   #@AutoGenerated
   ['Accounts':{'LOA Headcount'}]=C:IF(DTYPE('time_mqy', !time_mqy)@='C',
   IF(DIMIX('time_mqy', ATTRS('time_mqy', !time_mqy, 'lastperiod'))>0,
   DB('Payroll Model', !Measure, !Version, ATTRS('time_mqy', !time_mqy, 'lastperiod'), !regioncc, !cc_emp, 'LOA Headcount'),
   DB('Payroll Model', !Measure, !Version, ELCOMP('time_mqy', !time_mqy, ELCOMPN('time_mqy', !time_mqy)), !regioncc, !cc_emp, 'LOA Headcount')),
   CONTINUE);
   #EndRegion
   #Region Dimension:Accounts, Member:Temp Full Time Headcount
   #Autogenerated MEMBERCALC CONSOLIDATED 5B4163636F756E74735D2E5B54656D702046756C6C2054696D652048656164636F756E745D
   #@AutoGenerated
   ['Accounts':{'Temp Full Time Headcount'}]=C:IF(DTYPE('time_mqy', !time_mqy)@='C',
   IF(DIMIX('time_mqy', ATTRS('time_mqy', !time_mqy, 'lastperiod'))>0,
   DB('Payroll Model', !Measure, !Version, ATTRS('time_mqy', !time_mqy, 'lastperiod'), !regioncc, !cc_emp, 'Temp Full Time Headcount'),
   DB('Payroll Model', !Measure, !Version, ELCOMP('time_mqy', !time_mqy, ELCOMPN('time_mqy', !time_mqy)), !regioncc, !cc_emp, 'Temp Full Time Headcount')),
   CONTINUE);
   #EndRegion
   #Region Dimension:Accounts, Member:Temp Part Time Headcount
   #Autogenerated MEMBERCALC CONSOLIDATED 5B4163636F756E74735D2E5B54656D7020506172742054696D652048656164636F756E745D
   #@AutoGenerated
   ['Accounts':{'Temp Part Time Headcount'}]=C:IF(DTYPE('time_mqy', !time_mqy)@='C',
   IF(DIMIX('time_mqy', ATTRS('time_mqy', !time_mqy, 'lastperiod'))>0,
   DB('Payroll Model', !Measure, !Version, ATTRS('time_mqy', !time_mqy, 'lastperiod'), !regioncc, !cc_emp, 'Temp Part Time Headcount'),
   DB('Payroll Model', !Measure, !Version, ELCOMP('time_mqy', !time_mqy, ELCOMPN('time_mqy', !time_mqy)), !regioncc, !cc_emp, 'Temp Part Time Headcount')),
   CONTINUE);
   #EndRegion
#EndRegion

FEEDERS;
[{'Annual Salary','Base Salary','Current Daily Rate','Adjusted Daily Rate','Dept','Position Title','count', 'Cum Comp'}, 'Actual' ] => ['Forecast'];

['Amount', 'Forecast','time_mqy':'comment', 'Annual Salary' ] =>
   DB('Payroll Model', 'Amount', !Version, 'Total of Time_MQY', !regioncc, !cc_emp,'Base Salary'),
   DB('Payroll Model', 'Amount', !Version, 'Total of Time_MQY', !regioncc, !cc_emp,'H_Base Salary Days in Payroll') ;

['Forecast', 'Base Salary' ] =>  ['Current Daily Rate'],['Merit $'],['OT $'];
['Forecast', 'Current Daily Rate' ] => ['Adjusted Daily Rate'] ;
['Forecast', 'H_Base Salary Days in Payroll' ] =>  ['Current Payroll Daily Rate'],['60810200'];
['Forecast', 'Current Payroll Daily Rate' ] => ['Adjusted Payroll Daily Rate'] ;
['Forecast','Adjusted Daily Rate'] => DB('Payroll Model', 'Amount', !Version, !Time_MQY,
    !regioncc, !cc_emp,  'Adjusted Wage Calc'),
  DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_After' ),
    !regioncc, !cc_emp, 'Base Salary'  ) ;

['Forecast','Adjusted Payroll Daily Rate'] => DB('Payroll Model', 'Amount', !Version, !Time_MQY,
    !regioncc, !cc_emp,  'H_New Salary Days in Payroll');

['Forecast','Adjusted Wage Calc']=>['60810400'],['Key Contributor Amount'],['60940300'],['60960100'];

['Forecast','Total Compensation']=>['Cum Comp'],['FICA Amount'],['FMHI Amount'],['60820500'],['60820700'], ['60940500'];

['Amount','Forecast','CurOpenNew_Load'] =>
   DB('Payroll Model', 'Amount', 'Forecast', 'Total of Time_MQY', !regioncc, !cc_emp, 'Count');
['Count'] => ['60920600'], ['60940100'], ['60940200'] , ['60940400'],['Full Time Headcount'],
  ['Part Time Headcount'];

Re: Data Not Moving to Cell

Posted: Tue Jun 23, 2020 9:25 pm
by scrumthing
From a quick lock at the rules and feeders I would suggest you look at ['Amount', 'Forecast','time_mqy':'comment', 'Annual Salary' ]. That seems to kick it off. Check if that cells behaves differently. For everything else IMO someone has to check that cube in more detail. But it is already late so maybe I am mistaken.

Re: Data Not Moving to Cell

Posted: Wed Jun 24, 2020 2:38 am
by bbrady74
scrumthing wrote: ↑Tue Jun 23, 2020 9:25 pm From a quick lock at the rules and feeders I would suggest you look at ['Amount', 'Forecast','time_mqy':'comment', 'Annual Salary' ]. That seems to kick it off. Check if that cells behaves differently. For everything else IMO someone has to check that cube in more detail. But it is already late so maybe I am mistaken.
Thank you, Scrumthing. I have check the "comment" is populated as of Oct 2019 and that is the date my "Annual Salary" field is populated. This issue is pervasive for all the fields in January 2022 and forward.

Re: Data Not Moving to Cell

Posted: Wed Jun 24, 2020 1:12 pm
by ascheevel

Code: Select all

['Amount', 'Forecast','time_mqy':'comment', 'Annual Salary' ] =>
   DB('Payroll Model', 'Amount', !Version, 'Total of Time_MQY', !regioncc, !cc_emp,'Base Salary'),
   DB('Payroll Model', 'Amount', !Version, 'Total of Time_MQY', !regioncc, !cc_emp,'H_Base Salary Days in Payroll') ;
Is the element Jan 2022 a descendant of the consolidation Total of Time_MQY?

Re: Data Not Moving to Cell

Posted: Wed Jun 24, 2020 1:50 pm
by bbrady74
Yes, Jan 2022 is a descendant of Total of Time_MQY

Re: Data Not Moving to Cell

Posted: Wed Jun 24, 2020 2:18 pm
by ascheevel
Good. One test you could do is recreate the cube view from your initial screenshot with only the measures Adjusted Wage Calc, Adjusted Daily Rate, Current Daily Rate, Base Salary, and Annual Salary selected and then move your time dimension up to the context filter and turn zero suppression on. When you toggle between Dec 2021 and Jan 2022, are there any other rows besides Adjusted Wage Calc that disappear meaning they're not fed. Looking at your feeder statements, Adjusted Wage Calc is fed from Adjusted Daily Rate and Adjusted Daily Rate is fed from Current Daily Rate and Current Daily Rate is fed from Base Salary and Base Salary is fed from Annual Salary and Annual Salary is fed from what's inputted in time_mqy:comment. Somewhere in that feeder chain is a break and the zero suppression will hopefully help you see where.

Another questions: is the Month_After attribute populated for Dec 2021? If not, that would prevent Base Salary from feeding the next month in the feeder statement below. If it was only just recently populated, you may need to reprocess feeders on the cube.

Code: Select all

['Forecast','Adjusted Daily Rate'] => DB('Payroll Model', 'Amount', !Version, !Time_MQY,
    !regioncc, !cc_emp,  'Adjusted Wage Calc'),
  DB('Payroll Model', 'Amount', !Version, ATTRS('Time_MQY ' , !Time_MQY , 'Month_After' ),
    !regioncc, !cc_emp, 'Base Salary'  ) ;

Re: Data Not Moving to Cell

Posted: Wed Jun 24, 2020 2:30 pm
by bbrady74
Thank you for the response. I will try to some of the suggestions.

Re: Data Not Moving to Cell

Posted: Wed Jun 24, 2020 3:09 pm
by bbrady74
Thank you for the response. I tried re-processing the feeders and it worked :!: I have not had to do that in the past, but thank you for the suggestion!
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited