topic Re: Weekly head counts from SCD2 table in QlikView

Weekly head counts from SCD2 table

Anonymous — Fri, 29 Jun 2018 18:48:38 GMT

I have a requirement to publish monthly, weekly dashboard (kind of bar chart) from SCD type 2 table from start of year

my table is as below

EmpID	Name	Manager	Dept	Effective_dt	Expirty_Dt	Expired_IN
1	Raju	Ramesh	Oil	1-Jan-18	26-Jul-18	Y
2	Ram	John	Oil	1-Jan-18	27-Aug-18	N
3	Ramesh	Pillai	Oil	1-Jan-18	12-Dec-99	Y
1	Raju	Pillai	Oil	27-Jul-18	12-Dec-99	N
3	Ramesh	Jacob	Oil	28-Aug-18	12-Dec-99	N
4	Jacob	Ethan	Oil	28-Aug-18	12-Dec-99	N

I need to display a bar chart

with

Jan -3

Feb-3

Mar-3

Apr-3

May-3

Jun-3

Jul-3

Aug-4

Any suggestions?

Re: Weekly head counts from SCD2 table

arthur_dom — Mon, 02 Jul 2018 16:19:17 GMT

Hi Rao Kuntamukkala.

I was contacted on LinkedIn to come here and have a look in this question.

I think what you are triyng to do is something like the one that is attached. (below is a image)

If you need anything else, please contact me.

Beste regards,

Arthur

Re: Weekly head counts from SCD2 table

Anonymous — Fri, 06 Jul 2018 16:46:59 GMT

Thanks Arthur

Is there a way i can drill down using month filter on straight table data

Eg:

If I select month month jan - straight table should show 3 records and so on

thanks for your help

Re: Weekly head counts from SCD2 table

arthur_dom — Sat, 07 Jul 2018 17:44:51 GMT

yes... On the attached file, remove EmpId of the dimension part:

If your question is solved, please , mark as answered or helpful.

Best regards!

Re: Weekly head counts from SCD2 table

Anonymous — Sat, 07 Jul 2018 18:42:28 GMT

for some reason when a employee record is expired in same month and a new record is inserted with new effective date - the record is not counted in Jun month, please see below

Again, Many thanks for your help