Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
in a pivot table, how can I get min and max date for each month considering the MonthYear dimension is added to the table.
for example:
I have a Pivot table with the following dimensions:
MonthYear
Branch
I want to get the number of active employee at the beginning and at the end of each month to calculate attrition ration.
I tried the following expression:
number of employees at the beginning of the month:
count({<event_type={A},Date={"=Min(total<MonthYear>Date)"}>} Distinct EmpID)
number of employees at the end of the month:
count({<event_type={A},Date={"=Max(total<MonthYear>Date)"}>} Distinct EmpID)
but both expression return the total number of active employees in the month which is not what I want.
below is my data model, I create a record in Event_log table for everyday an employee is "Active"
if I understand,
add 2 flag in MasterCalendar for begin and end of month;
in expression you can use the flag (set analysis) to filter
count({<event_type={A},FlagStartMonth={1}>} Distinct EmpID)