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

# LOD and Calculated Fields

Hello everyone!!!

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)

Please you can find here attached an example.

Thank you,

Enrico

• ###### 1. Re: LOD and Calculated Fields

Good morning

you could use the first formula below to give you the individual totals and grand total to properly sum them or the second formula to give you the proper fixed overall total

then return this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: LOD and Calculated Fields

Hi, Enrico

Please find my solution attached as well as screenshot below as reference.

Hope this helps

ZZ

1 of 1 people found this helpful