7 Replies Latest reply on Jul 25, 2018 7:38 PM by Deepak Rai

# LOD Difference not showing correctly

I have 2 LOD expressions where I do a unique count over the employee ID  if the employee is  added or termed.  I then find the difference between the "Add LOD" minus the "Term LOD".   Some of the calculations are problematic.  For example, employee ID = 16 which has the role of CEO and it has one "Term" record and no "Add" record. The difference shows zero but I want it to show -1.  See the attached workbook for details.  Thanks you!!

• ###### 2. Re: LOD Difference not showing correctly

Hi see the attached

try this

it returns 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.

• ###### 3. Re: LOD Difference not showing correctly

Deepak,

It didn't work when I added ZN(SUM(Add LOD))-ZN(SUM(Term LOD)).   Can you show me on the tableau workbook that I attached?

• ###### 4. Re: LOD Difference not showing correctly

Jim,

Thank you so much for all of your help!!  It worked!!!  Can you explain how it works?

• ###### 5. Re: LOD Difference not showing correctly

sure

the LOD function works to create combinations of the dimensions of the variables that precede the colon (:) and then aggregates them by what follows

the result is a series of values that are NOT aggregates in themselves but are at a higher level than the base data - the LOD can then be used in other calculations including other lod's or table calculation - ok so what

the original calculations count distinctly the a number that is the employee id or 0 for all combinations of industry,state and role where type as add

{FIXED [Industry],[State],[Role]:COUNTD( IF ([Type] = 'Add') then  [Employee ID] else 0 end) }

this calculation looks at the same combinations  and counts the EMP ID's that meet the combination criteria

{FIXED [Industry],[State],[Role]: ( IF min([Type]) = 'Add' then COUNTD([Employee ID]) else 0 end )}

then when it is brought into the formula below you sum the count

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.

• ###### 6. Re: LOD Difference not showing correctly

Jim,

I am noticing that the unique Dcount for Manager is incorrect.  The dashboard that you modified shows 6 Unique Employee ID's for  Adds and 3 Ids for Terms but it should be 3 Adds and 1 Term for of a difference of 2.  The adds is IDs:13,24,49 and the Term ID is 66.

 Industry Role Type State Employee ID Real Estate Manager Add VA 13 Real Estate Manager Add NC 13 Energy Manager Add NY 24 Education Manager Add OH 49 Real Estate Manager Add OH 49 IT Manager Add OH 49 Real Estate Manager Terms IL 66 Energy Manager Terms IL 66 Regional Manager Terms IL 66

• ###### 7. Re: LOD Difference not showing correctly

Hi,

Since you are looking at the level of Role, I think you need this:

Thanks

Deepak