Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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