Active headcount overtime

[Anonymous]

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

[#2]

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

[Anonymous]

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