Help Moving a Set Analysis to Load Script

[#1]

Hi Everyone,

I need some help moving this set analysis to the load script. So I will use the Employees table as Resident and I will want to group by CalendarMonthName. Can someone construct this Set Analysis in Load script? I am going to build this for each month and it is a count of employees here at the end of each month.

TEMP:

Load

if(ClanedarMonthName) = 'Jan',

Count(EmployeeDirectory.ID)

          Where NLE_Period=vJanPer

That is as far as I get because of the subtraction and addition that gets to happen. This set analysis works for expressions and now I get to put the fields in the script so that I can build a trending by month compared to other values. So I want a statement that accomplishing the same thing for each month.

if (([CalendarMonthName])='Jan',(count({<NLE_Period={'$(vJanPer)'}>}  EmployeeDirectory.ID))/(count(EmployeeDirectory.ID)-(Count ({<NLE_MonthEnd={'<=$(vJan)'},DOH_MonthEnd={'<=$(vJan)'}>}  EmployeeDirectory.ID)+(count({<DOH_Period={'>$(vJanPer)'}>}  EmployeeDirectory.ID))))

RESIDENT

     Employees

High Regards to anyone who assists me,

OnePuttGirl

[#2]

Hi OnePuttGirl

Here's a potential solution.

If I have understood correctly you need to be able to calculate the number of employees you have in your business at the end of each month.

I will assume that your Employees table contains the following fields:

• Employee ID
• Employee Start Date
• Employee Leave Date (Blank if employee is still employed)

You start by loading this table twice, once to capture the start dates and a second time to capture the leave dates.  The added 'Type' field allows this distinction to be achieved.  As part of this script you can establish common date fields with Month_Year being very useful for your particular analysis.

You then use the group by function in mapping tables to count the number of starters and leavers in each Month_Year.

Next you use the ApplyMap function to create a table that contains all the data you need in one place, i.e.

• Month_Year
• Count of Starters for each Month_Year
• Count of Leavers for each Month_Year
• Calculated balance of starters-leavers

Finally you need to set up a straight table adding the above-mentioned four fields as dimensions then use an expression to calculate the cumulative balance.

The expression for calculating the cumulative balance is:

rangesum(above(Total sum(Balance),0,RowNo(Total)))

The script to create the tables is below:

Employees:

LOAD ID,

     [Start Date] as Date,

     'Starters' as Type,

     Month([Start Date]) as Month,

     Year([Start Date]) as Year,

     Date(MonthStart([Start Date]),'MMM-YYYY') as Month_Year

FROM

Employees.xls

(biff, embedded labels, table is Sheet1$);

Concatenate

LOAD

           ID,

     [Leave Date] as Date,

     'Leavers' as Type,

     Month([Leave Date]) as Month,

     Year([Leave Date]) as Year,

     Date(MonthStart([Leave Date]),'MMM-YYYY') as Month_Year

FROM

Employees.xls

(biff, embedded labels, table is Sheet1$);

Starters_Map:

Mapping Load

Month_Year,

Count(ID) as Count_of_Starters

Resident Employees

Where Type='Starters'

Group By Month_Year

;

Leavers_Map:

Mapping Load

Month_Year,

Count(ID) as Count_of_Leavers

Resident Employees

Where Type='Leavers'

Group By Month_Year

;

Results:

Load distinct

Month_Year,

ApplyMap('Starters_Map',Month_Year,0) as Starters,

ApplyMap('Leavers_Map',Month_Year,0) as Leavers,

ApplyMap('Starters_Map',Month_Year,0)-ApplyMap('Leavers_Map',Month_Year,0) as Balance

Resident Employees

Order By Month_Year Asc;

The output from this example is shown below:

Month_Year	Starters	Leavers	Balance	Cumulative Balance
Sep-2011	4	0	4	4
Oct-2011	4	0	4	8
Nov-2011	0	1	-1	7
Dec-2011	0	1	-1	6
Jan-2012	8	0	8	14
Feb-2012	3	1	2	16
Apr-2012	0	1	-1	15
May-2012	6	3	3	18
Jun-2012	0	1	-1	17
Jul-2012	0	1	-1	16
Aug-2012	0	1	-1	15
Sep-2012	5	8	-3	12
Oct-2012	0	1	-1	11

Hope that helps

Best wishes

Steve