11 Replies Latest reply: Mar 15, 2016 11:48 AM by neetha P RSS

    Active headcount overtime

    neetha P

      Hi All,

       

      Please help needed for active headcount for past 12 months.

       

      Active employee overtime.PNG

      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

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

          • Re: Active headcount overtime
            Lokeshwari N

            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
              neetha P

              Hi All,

               

              Taken a workaround using calculated dimension:

               

              Active emp overtime.PNG

              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