Need help to calculate the headcount

[qliktoview123]

Hello,

I am calculating the headcount on the basis of effective date and empl status(= A).I have created Year and month using the effective date.when I am selecting the current year the headcount is around 3191,thats fine but as soon as I select the previous year its giving around 25947. The previous year count is coming wrong, it has to be 3461.

Please help me out .

I have shared the screenshot also.

[sunny_talwar]

Would you be able to share your application to check it out?

[muthukumar77]

Hi,

Can you send me Expression using in that text object?

[sunny_talwar]

If I do a Distinct Count, I see a lower number, but it still doesn't match what you want to see. But it also impact the 2016-2017 selection.

Expression: =Count(DISTINCT EMPID)

2015-2016 Selected

2016-2017 Selected

Could this be what you are looking for?

[qliktoview123]

Nope...the count for 2016-17 is around 3500 and for 2015-16 is 3461

[sunny_talwar]

If the total observation in your application is 31026 and one selection gives you 3k, why do you not expect the other to give 27k+? I am not sure what I might be missing here.

[qliktoview123]

SELECT Count(*)

FROM Headcount A

WHERE A.EMPL_RCD=0

AND A.EFFDT = (SELECT Max(EFFDT) FROM Headcount WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT <='sysdate')

AND A.EFFSEQ = (SELECT Max(EFFSEQ) FROM Headcount WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT)

AND A.EMPL_STATUS = 'A';

This is the SQL query need to be implemented

[Colin-Albert]

If you test your expressions using a chart rather than a text box it is easier to see where the figures are coming from.

Are you sure the data in your headcount.qvd is correct, as you have a count of almost 26,000 for EMP_STATUS A in 2015-2016?

[qliktoview123]

SELECT Count(*)

FROM Headcount A

WHERE A.EMPL_RCD=0

AND A.EFFDT = (SELECT Max(EFFDT) FROM Headcount WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT <='sysdate')

AND A.EFFSEQ = (SELECT Max(EFFSEQ) FROM Headcount WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT)

AND A.EMPL_STATUS = 'A';

This is a SQL query given by my client...I need to implement this query in qlikview.

[Colin-Albert]

So how was the QVD created? What data does the QVD select?

This query only selects data for EMPL_STATUS = 'A', but your QVD has other statuses.