topic Re: Active headcount overtime in QlikView

Active headcount overtime

Anonymous — Wed, 09 Mar 2016 15:19:15 GMT

Hi All,

Please help needed for active headcount for past 12 months.

Below is my expression:

=Count({<[HIRE DATE] = {"<=$(=AddMonths(MonthStart(Today()),-13))"}, [TERMINATION DATE]= {">=$(=AddMonths(Monthend(Today())))"} >}[EMP COUNT])+
(Count({<[HIRE DATE] = {"<=$(=AddMonths(MonthStart(Today())))>=$(=AddMonths(Monthend(Today()),-12))"},[HIRE COUNT] = {'1'} >}[EMP COUNT])-
Count({<[TERMINATION DATE] = {"<=$(=AddMonths(MonthStart(Today())))>=$(=AddMonths(Monthend(Today()),-12))"},[TERMINATION COUNT] = {'1'} >}[EMP COUNT]))

Getting wrong headcount.

please see attached QVW

Thanks

neetha

Re: Active headcount overtime

sunny_talwar — Wed, 09 Mar 2016 15:23:39 GMT

What are you expecting to see? Do you know where the issue might be from your initial investigation?

Re: Active headcount overtime

Anonymous — Wed, 09 Mar 2016 15:36:07 GMT

Hi Sunny,

Thanks for quick response.

I want overall headcount for every month, add hires and subtracting terminations.

The logic I tried is wrong I feel, it gives in thousands ,where It should be over 1 lakh or more for each month.

Thanks

Neetha

Re: Active headcount overtime

sunny_talwar — Wed, 09 Mar 2016 15:39:31 GMT

Have you looked at each of the three expressions individually and tried to see if one of them is acting weird or if all of them seem incorrect? The problem is that from the looks of it, your expression looks right. I don't see any issue with them. So unless I have a sample to look at, I will only be able to guide you and not provide anything concrete.

Re: Active headcount overtime

sunny_talwar — Wed, 09 Mar 2016 15:43:46 GMT

One thing you can check is if your set analysis is in the same format as HIRE DATE? May be add this to a text box object -> =AddMonths(MonthStart(Today()),-13) and see if it is in the same format as HIRE DATE or not. If it isn't then add a Date() function with the matching format in all your set analysis

Re: Active headcount overtime

Anonymous — Wed, 09 Mar 2016 16:53:38 GMT

Hi Sunny,

Please give your mailid.

Thanks Neetha

Re: Active headcount overtime

avinashelite — Wed, 09 Mar 2016 17:11:31 GMT

Hi Neetha,

As sunindia‌ suggested try to break our expression into 3 part and then check if everything i fine....then combine the expressions

<=$(=AddMonths(MonthStart(Today()),-13))"},[TERMINATION DATE]= {">=$(=AddMonths(Monthend(Today())))"} >}[EMP COUNT])+

// is this portion is rite it should be

> than 12 month and less than today !

>=$(=AddMonths(MonthStart(Today()),-13))"},[TERMINATION DATE]= {"<=$(=AddMonths(Monthend(Today())))"} >}[EMP COUNT])

Re: Active headcount overtime

Anonymous — Thu, 10 Mar 2016 13:17:04 GMT

Hi Avinash,

Thanks for reply.

Requirement - Whole HC Based on MonthYear dimension for last 12 months.

for each data point it should give total active headcount for that month.

which I am not getting.

Thanks & Regards

Neetha

Re: Active headcount overtime

MarcoWedel — Thu, 10 Mar 2016 22:57:04 GMT

Hi,

please post example applications in your thread instead of mailing them to individual community members, so others can follow and learn.

Maybe this post helps:

Preparing examples for Upload - Reduction and Data Scrambling

thanks

regards

Marco

Re: Active headcount overtime

Anonymous — Fri, 11 Mar 2016 11:49:01 GMT

HI Marco,

I have attached QVW .

Please look at it.

Thanks

Neetha

Re: Active headcount overtime

Fri, 11 Mar 2016 12:25:48 GMT

Hi,

In your expression might be count is wrong because of this mentioned in bold

=Count({<[HIRE DATE] = {"<=$(=AddMonths(MonthStart(Today()),-12))"}, [TERMINATION DATE]= {">=$(=AddMonths(Monthend(Today())))"} >}[EMP COUNT])+

(Count({<[HIRE DATE] = {"<=$(=AddMonths(MonthStart(Today())))>=$(=AddMonths(Monthend(Today()),-12))"},[HIRE COUNT] = {'1'} >}[EMP COUNT])-

Count({<[TERMINATION DATE] = {"<=$(=AddMonths(MonthStart(Today())))>=$(=AddMonths(Monthend(Today()),-12))"},[TERMINATION COUNT] = {'1'} >}[EMP COUNT]))

try using

AddMonths(MonthStart(Today()),0) and check

Re: Active headcount overtime

Anonymous — Tue, 15 Mar 2016 15:46:27 GMT

Hi All,

Taken a workaround using calculated dimension:

Calculated Dimension:

=ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)

Expression:

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear1,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-1),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear2,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-2),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear3,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-3),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear4,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-4),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear5,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-5),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear6,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-6),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear7,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-7),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear8,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-8),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear9,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-9),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear10,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-10),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear11,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-11),'DD/MM/YYYY'))"}>}[EMP COUNT]),

If(ValueList(vMonthYear0,vMonthYear1,vMonthYear2,vMonthYear3,vMonthYear4,vMonthYear5,vMonthYear6,vMonthYear7,vMonthYear8,vMonthYear9,vMonthYear10,vMonthYear11,vMonthYear12)=vMonthYear12,count({<CET1 = {'S'},[WORKER STATUS] = {'Active'},[EFFECTIVE DATE] = {"<=$(=Date(AddMonths(Monthend(Today()),-12),'DD/MM/YYYY'))"}>}[EMP COUNT])

))))))))))))

Its working ,but the chart is not filtering as required , any suggestions.

it should filter with other charts,for example by monthyear when selected in other charts.

Thanks & Regards

Neetha