Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
anavalera
Contributor
Contributor

setanalysis with a dimension

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!

3 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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

Demlet
Contributor III
Contributor III

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
Contributor
Contributor
Author

Thanks Derek,


We will try your suggestion.