topic Re: Headcount measure using disassociated calendar table in App Development

Headcount measure using disassociated calendar table

Newbie777 — Fri, 23 Jul 2021 13:55:28 GMT

Hello,

I am trying to create headcount measure using the employee table and with the information of hiring date and leaving date and the calendar table which are disassociated with each other to enable dynamic calculation based on the different points in time dimension. I used the following measure hoping to see the HC to be calculated flexibly with respect to different points in time dimension, but the outcome of the calculation is zero. I would appreciate it if anyone can point me in the right direction where I am going wrong with this measure.

Thanks a lot for your help.

Re: Headcount measure using disassociated calendar table

Kushal_Chawda — Fri, 23 Jul 2021 14:20:43 GMT

@Newbie777 try below

=if( max(Total MonthYear) >= [Hiring date] and max(Total MonthYear) <= [Leaving date], count(..))

Re: Headcount measure using disassociated calendar table

Newbie777 — Fri, 23 Jul 2021 16:08:57 GMT

Thanks for your response, but I am getting the zero as the answer. I guess it could be due to the fact that there is no MonthYear dimension in my data model. I tweaked it a bit, and tried

if( max(Total [Month Year])>=Alt(date([Hiring date]), today(0)) and max(Total [Month Year])<=Alt(date([Leave date]), today(0)),count([First name last name]),0)

but it doesn't produce non-zero answer either. I'd greatly appreciate it if you could let me know what I am doing wrongly.

Thanks a lot!

Re: Headcount measure using disassociated calendar table

Kushal_Chawda — Fri, 23 Jul 2021 20:29:40 GMT

@Newbie777 Do you have sample data? Without looking at your data it will be difficult to tell what is wrong

Re: Headcount measure using disassociated calendar table

Newbie777 — Sat, 24 Jul 2021 03:34:15 GMT

Thanks for your response, but it is not easy to share the data as I need to replace the content with fake data.

The expression below is producing non-zero answer, but it just seems to be count of all the people on the database without reference to time dimension.

if( today(0)>=Alt(date([Hiring date]), today(0)) and today(0)<=Alt(date([Leave date]), today(0)),count([First name last name]),0)

Re: Headcount measure using disassociated calendar table

Stinnett14 — Sat, 24 Jul 2021 06:20:38 GMT

The calendar table is a table I created to create a relationship between the 3 tables based on dates.

Omegle

Re: Headcount measure using disassociated calendar table

Newbie777 — Sat, 24 Jul 2021 13:16:42 GMT

Thanks, but the way I thought this would work is not by creating a relationship between calendar table and employee database. If I created relationship with calendar table with either hiring date and leaving date it will not work. So I specifically disassociated the tables, like I would do in PowerPivot model. I am not familiar with Qlik Sense, but I would imagine that it will work in a similar way with Power BI in terms of headcount calculation.

Re: Headcount measure using disassociated calendar table

Newbie777 — Sat, 24 Jul 2021 14:14:03 GMT

I couldn't find the reason after spending several hours on this issue. I'd greatly appreciate it if you could let me know why the measure expression below is not working, and just producing 0 as the calculated result for all the time periods. I attach the sample qvf file. I'd greatly appreciate it if you could check the master measure I created named "Headcount". I've kept calendar table and employee table disassociated.

Re: Headcount measure using disassociated calendar table

Newbie777 — Sat, 24 Jul 2021 14:17:43 GMT

Here is the qvf file with the sample data. It contains 4 tables but only two are relevant for this measure. I tried to delete the two other irrelevant tables, but I couldn't, sorry.

Re: Headcount measure using disassociated calendar table

Newbie777 — Sun, 25 Jul 2021 05:31:48 GMT

Hello Kushal_Chawda,

I'd greatly appreciate it if you could have a look at the qvf file and let me know why I am getting zero as a calculation result. Thank you in advance for your time.

Re: Headcount measure using disassociated calendar table

Kushal_Chawda — Sun, 25 Jul 2021 13:55:56 GMT

@Newbie777 what is the expected output you need?

Re: Headcount measure using disassociated calendar table

Newbie777 — Sun, 25 Jul 2021 14:14:58 GMT

Thanks for your response. I'd like to create a dashboard like below, but at the moment, I am focusing on the table headcount table at the bottom left. I'd like the headcount to be calculated for all the selected years and months in the table flexibly using calendar table as the time dimension. Once I get the measure expression to be working, I guess I can flexibly use it to apply to any visualization template in Qlik Sense, so I am not fixated with that particular table format though. I just want the headcount measure to be working which calculates the headcount at any selected date using after hiring date and before leaving date if expression.

Re: Headcount measure using disassociated calendar table

Newbie777 — Mon, 26 Jul 2021 15:52:10 GMT

Hi Kushal_Chawda,

I'd greatly appreciate if you could let me know where I am going wrong with the formula to get zero result. FYI the calendar table might be mismatching with the date range in the sample data, but in my real data too, even though this is not happening, the formula doesn't work.

Thanks for your help in advance.

Re: Headcount measure using disassociated calendar table

Newbie777 — Sat, 31 Jul 2021 01:36:55 GMT

Hello Experts,

Please let me know how to fix the measure expression in my post above to get it working. I haven't been able to resolve this issue yet. Thanks a lot for your time in helping me solve this puzzle.

Re: Headcount measure using disassociated calendar table

Kushal_Chawda — Sat, 31 Jul 2021 14:29:47 GMT

@Newbie777 what is your logic to get the headcount?

Re: Headcount measure using disassociated calendar table

Newbie777 — Sat, 31 Jul 2021 14:53:17 GMT

Hi Kushal_Chawda,

My logic to get the headcount is that at any point in time headcount is the count of number of employees from the employee database who were hired before that selected point in time, and who have not left the company at that selected point in time. By this way, we can get the number of headcount at any point in time in historical records if we maintain employee database properly. I sometimes see headcount example with active (who are employed at one particular point in time) and not active (who have left at that time), but this kind of counting only is able to count employee at a particular point in time, and this is not what I want. I'd like my measure to be able to deal with the headcount counting at any selected point in time from the employee database which contains the information of hiring and leaving dates.

Therefore, I use disassociated calendar table and greater than equal to (>=) hiring date and less than or equal to leaving date. Please let me know if you need any further clarification. I am keen to resolve this issue, but I am just a beginner Qlik Sense user and I don't know how to do this in Qlik Sense, which I am able to in Excel Power Pivot formula...