Hi all. Thanks a lot to be there.
I do not have much experience. If this question is resolved in another thread, I am very sorry. I have tried to locate something before writing to you.
Let me explain my question:
I'm creating a measure. From a functional point of view the idea is to calculate all the employees who have been working on the company during the WHOLE calculation period. To solve this problem, I am going to calculate the employees hired at the beginning of the period, I am going to make the intersection with the employees who are still hired at the end of the period and from this set I am going to remove the employees who were off at least one day in the period.
Set of active employees at first day of the year of period calculation
<EmployeeID = P ({<FACTTYPE=,IDCalendar={'$(=$(vdateFirstDayInYear))'},EmploymentStatusID={'1'}>} EmployeeID ) >
Set of active employees at the end of period calculation
<EmployeeID = P ({<FACTTYPE=,IDCalendar={'$(=$(vdateMaxDayInPeriod))'},EmploymentStatusID={'1'}>} EmployeeID ) >
Set of employees in TERMINATION in the period of calculation
<EmployeeID = P ({<FACTTYPE={'EXITS'}, IDCalendar={">=$(=$(vdateFirstDayInYear)) <=$(=$(vdateMaxDayInPeriod))"}> } EmployeeID ) >
Here is my question:
In the measure's formule, I'd like to have something like:
Count( {((GroupHiredStartPeriod) * (GroupHiredEndPeriod)) - GroupTerminationInPeriod } Distinct EmployeeID )
Just to test, in the application I have tried to create a variable GroupHiredStartPeriod and in the formule section, include the code:
<EmployeeID = P ({<FACTTYPE=,IDCalendar={'$(=$(vdateFirstDayInYear))'},EmploymentStatusID={'1'}>} EmployeeID ) >
It doesn't work...
Then, my first question is how I must create a variable or any other element in QlikSense (perhaps a Group?????), to be reused in any measure.
My second question is the following: I don't know if we can do that directly in the Qlik App or it's absolutely mandatory to pass throug an script to load the definition of these variables. If so, could you please provide an example for creating one of above variable?
I'd like to thank you in advance your help and support.
Have a nice week.
Ángel.
