setanalysis with a dimension

[anavalera]

Hi everybody!

I'm trying to calculate the employee turnover. I need this data related to the same year period of last year. For example, currently (agoust 2018), I will need the employee turnover in the period jan-2017 - jul-2017. Next month I will need jan-2017 - ag-2017.

The formula I used for calculate the turnover of the hole last year (2017) year is:

Sum( {< [Calendar Type Code] = {1}, Year = {'$(vL.PeriodMaxYearM1)'},

        [Leave Flag] = {1} >} [Worker Counter] )

/

Count ( {<  [Calendar Type Code] = {1}, Year = {'$(vL.PeriodMaxYearM1)'},

>} [Worker Personal Counter] )

This formula works properly.

The problem is that I need the same info, but not of the hole 2017, but just the same months that in the acual year (january-july). I've created a new dimension (Mes Acumulado Anual), which stablish that, if the month is lower or equal to the current month, then the value is 1. If the month is greater than the current month, then the value is 0. I'll show you an example:

Month Number	Mes Acumulado Anual
1	1
2	1
3	1
4	1
5	1
6	1
7	1
8	0
9	0
10	0
11	0
12	0

As you can check, the dimension works properly. I've tried to add this dimension to the set analysis of the turnover metric:

Sum( {< [Calendar Type Code] = {1}, Year = {'$(vL.PeriodMaxYearM1)'}, [Mes Acumulado Anual] = {1},

[Leave Flag] = {1} >} [Worker Counter] )

/

Count ( {<  [Calendar Type Code] = {1}, Year = {'$(vL.PeriodMaxYearM1)'}, [Mes Acumulado Anual] = {1}

>} [Worker Personal Counter] )

The problem is that the new argument does not change anything.

Could anybody help me?

Thank you!

[felipedl]

Dont quite understand what you need, can you rephrase your problem with an example?

[Demlet]

Use a date table, and use them in your set analysis. Floor(yourDate), as YourDateField, then run it through YourDateTable in your script. It sounds like you need to use YTD-FLAG for current year. This will display the data up to your current date. For last years data use LYTD_FLAG. Modify/Create your own flags as needed.

Let vStartDate=Floor(MakeDate(2016,1,1));

Let vEndDate=Floor(Today());

Let vDiff=vEndDate-vStartDate+1;

YourDateTable:

Qualify*;

Unqualify YourDateField;

Load *,

  Date(MonthStart(YourDateField), 'YYYY-MM') As YearMonth,

  Year & '-' & Quarter As YearQuarter,

  WeekYear & '-' & Num(Week, '00') As YearWeek;

Load

  YourDateField,

  Year(YourDateField) As Year,

  Month(YourDateField) As Month,

  Date(YourDateField) As Date,

  Day(YourDateField) As Day,

  Week(YourDateField) As Week,

  'Q' & Ceil(Month(YourDateField)/3) As Quarter,

  WeekYear(YourDateField) As WeekYear,

  -Year2Date(YourDateField) As YTD_Flag,

  -Year2Date(YourDateField, -1) As LYTD_Flag,

  If( DayNumberOfQuarter(YourDateField) <= DayNumberOfQuarter(Today()), 1, 0) as IsInQTD,

  If( Day(YourDateField) <= Day(Today()), 1, 0) as IsInMTD,

  If( Month(YourDateField) = Month(Today()), 1, 0) as IsCurrentMonth,

  If( Month(AddMonths(YourDateField,1)) = Month(Today()), 1, 0) as IsLastMonth,

  If( DayNumberOfYear(YourDateField) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD;

Load

  RecNo()-1+$(vStartDate) As YourDateField

AutoGenerate($(vDiff));

Hope this helps

-Derek

[anavalera]

Thanks Derek,

We will try your suggestion.