2 Replies Latest reply on Apr 27, 2018 6:28 AM by Zhouyi Zhang

    LOD and Calculated Fields

    Enrico Serafini

      Hello everyone!!!

       

      I strongly need your help.

       

      I'm working with the following dataset:

      - Database with: ID, Month, Year, Type of Conctract

      - Leaver: with ID and Termination Date --> a separeted database with leavers from the same company

       

      After a join between the two datasets, I need to visualise:

      - Total number of Leavers YTD (from January to March)

      - Number of leavers YTD (from January to March) with Permanent Contract

      - Number of leavers YTD (from January to March) with Fixed Term Contract

       

      I managed to visualise correctly the total number of leavers with an LOD and a Calculated Fields.

       

      However, when I aggregate the leavers YTD by Type of Contract, a person that changed type of contracts during the last three months will be counted twice (both for the months in which he/she had a Fixed Term and for the months in which he/she had Permanent).

       

      So for example, let's say we have 2 leavers and 1 of them change type of contract from fixed to permanent in the last three months:

      - Total: 2 (it correctly counts the total number of Leavers YTD)

      - Permanent: 2 (It correctly counts 2 beacuase the 2 Leavers left the company when they had a Permanent contract)

      - Fixed: 1 (one of them has been counted again because he/she had a fixed term contract in the last three months)

       

      Could you please help me out?

       

      Please you can find here attached an example.

       

      Thank you,

      Enrico