3 Replies Latest reply: Mar 28, 2014 11:03 AM by Manish Kachhia RSS

    Count for every month even if there is no occurrence

    Suvi Ahlstedt

      Hi all,

      I have table which contains employee hire date, end date, employeeID and state (1=active, 0=no-active). Now I need a KPI =count({<State={'1'}>}EmployeeID) per month and it´s easy ofcourse add that count expression but it shows only count for months where employee has hired but I need count for every month.

       

      Example:

      EmployeeID     HireDate     EndDate     State

      1                    1.9.2013       -                  1

      2                    1.10.2013     -                  1

      3                    1.11.2013     -                  1

      4                    1.12.2013     -                  1

      5                    1.2.2014      -                   1

       

      I tried something like this =aggr(NODISTINCT( count({<State={'1'}>}EmployeeID)),Year) but it shows only count of hired person per month, not total count per month.

      What should I do to solve this?

      Thank you!