Here is the issue:
1. We have 2 different Master Calendars
a. Master Calendar used to reference Employee status information - (MonthYear)
i. Position
ii. Department
iii. Location
iv. Company
b. Master Calendar used to reference Payroll data - (CheckMonthYear)
2. Employee was terminated in November 2014, but got paid in January 2015.
We needed to figure out a way to reference the employee data using the Employee Master Calendar to reference the position/dept..etc, but align it with the payroll data from January 2015. We were able to use the Set Analysis / max function to get the most current employee position data and filter on Payroll Master Calendar for January 2015:
(This is being applied on the Data Validation Tab)
=sum({$<
CheckYear = {$(=Max(CheckYear))},
MonthYear={"$(=date(max(MonthYear),'MMM-YYYY'))"},
PrimaryPositionYN={1},
NonCashYN = {0}
EarningsHistoryYN -= {1}
>}
[Current Earnings])
This formula works (mostly) in a Straight Table if we filter on EmployeeID first, and then filter on Position and then CheckYear), but if we select Position or CheckYear first and then Employee, we get the incorrect value. I feel like we are going to need to use the aggr() function, but I’m not familiar enough with that function to make it work properly.
If I apply this function to a bar chart (Gross Wages by Position), it does not work properly at all. Here is the function I’m using on the Bar Chart, and it’s close to being accurate but not all the time:
(This is being applied on the Gross Wages Tab)
= sum({$<
CheckYear = {$(=Max(CheckYear))},
PrimaryPositionYN={1},
EarningsHistoryYN -= {1},
NonCashYN = {0}
>}
[Current Earnings])
The correct value for Jan-2015 – Sales Representatives should be $352,786.33 , but the value being displayed is $354,692.25
Attached is a copy of the sample QVD.
