Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Would you be able to share your application to check it out?
Hi,
Can you send me Expression using in that text object?
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?
Nope...the count for 2016-17 is around 3500 and for 2015-16 is 3461
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.
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
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?
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.
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.