Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm making a staff-app but have encounterd a little problem regarding staff that end their employment.
Lets say I have a table similar to this;
Staff_tmp: LOAD * INLINE [ Id, Name, Hired_date, End_date, Value 1, Arnold, 2018-01-01, , 8 2, Steven, 2018-01-01, , 8 3, Cloude, 2018-01-01, , 8 4, Emma, 2018-01-01, , 8 5, Sara, 2018-01-01, 2018-06-01, 8 6, Donald, 2018-01-01, , 8 7, Louise, 2018-01-01, , 8 8, Roland, 2018-01-01, 2018-10-01, 8 9, Thomas, 2018-01-01, , 8 ];
What I want is;
1. When the user select ex. july, a textlabel show the number of employees that month (8 since Sara ended in june).
Thanks in advance!
count( {<
Hired_date = {"<=$(=min(%Date))"} ,
End_date= {">=$(=max(%Date))", ''}
>} distinct Id)
This expression will work on the script below given that you have a data island calendar with a date field called %Date. The tricky part here is to fetch the empty End_date. You can not use this if the value is NULL(). I would as in my earlier example set the value to today or tomorrow for the field used in the set.
Staff_tmp: LOAD * INLINE [ Id, Name, Hired_date, End_date, Value 1, Arnold, 2018-01-01, , 8 2, Steven, 2018-01-01, , 8 3, Cloude, 2018-01-01, , 8 4, Emma, 2018-01-01, , 8 5, Sara, 2018-01-01, 2018-06-01, 8 6, Donald, 2018-01-01, , 8 7, Louise, 2018-01-01, , 8 8, Roland, 2018-01-01, 2018-10-01, 8 9, Thomas, 2018-01-01, , 8 ];
Sorry for not pointing that out, i DO have a master calender. I see I was a bit unclear (it was very clear in my head ).
What I need help with is the set analysis to show number of employees at a certain month taken account for when they start and end their employmeent.
Somthing like this (I know its wrong but just to show you want I need);
Count({<%Date<=End_date + %Date>=Start_date>} distinct ID)
count( {<
Hired_date = {"<=$(=min(%Date))"} ,
End_date= {">=$(=max(%Date))", ''}
>} distinct Id)
This expression will work on the script below given that you have a data island calendar with a date field called %Date. The tricky part here is to fetch the empty End_date. You can not use this if the value is NULL(). I would as in my earlier example set the value to today or tomorrow for the field used in the set.
Staff_tmp: LOAD * INLINE [ Id, Name, Hired_date, End_date, Value 1, Arnold, 2018-01-01, , 8 2, Steven, 2018-01-01, , 8 3, Cloude, 2018-01-01, , 8 4, Emma, 2018-01-01, , 8 5, Sara, 2018-01-01, 2018-06-01, 8 6, Donald, 2018-01-01, , 8 7, Louise, 2018-01-01, , 8 8, Roland, 2018-01-01, 2018-10-01, 8 9, Thomas, 2018-01-01, , 8 ];
Another approach, using a generated Fact table and the aggr() function.