12 Replies Latest reply on Aug 17, 2018 5:47 AM by Wendy Weber

# Calculate difference between two LOD calculations when user selects "ALL"

I have two LOD calculations below which count the distinct number of employee ids based on if they were added or termed.  I have a 3rd calculation called LOD Difference which finds the difference between the LOD Adds and LOD Terms.

I set up  2 parameters where the user can select the Healthplan and Product.  The LOD difference works perfectly unless the user selects "ALL" for the Product and then the LOD difference isn't correct. Should the LOD Difference calculation be calculated in a different way if the user selects "ALL" for the product in the scenario that they don't choose a specific product?  The parameters do filter the sheet so that isn't the issue.  Thank you for all suggestions in advance!!

{FIXED [Health Plan],[Role],[Product],[Type]: (

IF min([Type]) = 'Adds' then COUNTD(

[Employee ID]) else 0 end )}

LOD Terms:

{FIXED [Health Plan],[Role],[Product],[Type]: (

IF min([Type]) = 'Terms' then COUNTD(

[Employee ID]) else 0 end )}

LOD Difference: ([LOD Adds] - [ Lod Terms]

• ###### 1. Re: Calculate difference between two LOD calculations when user selects "ALL"

Wender,

Can you try putting parameter filters as context filters. Also in both of your calculation "else 0" should be changed to "else NULL" . else 0 will give one extra count.

{FIXED [Health Plan],[Role],[Product],[Type]: (

IF min([Type]) = 'Adds' then COUNTD(

[Employee ID]) else 0 end )}

• ###### 2. Re: Calculate difference between two LOD calculations when user selects "ALL"

Hi Wendy - without seeing the data have you tried this

diff = sum(([LOD Adds]) -sum( [ Lod Terms])

LOD expressions are NOT aggregates in themselves and need to be aggregated when you use them in other calculations

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: Calculate difference between two LOD calculations when user selects "ALL"

I tried the suggestions that you mentioned but it didn't have any impact on the outcome.  Thanks.

• ###### 4. Re: Calculate difference between two LOD calculations when user selects "ALL"

please post your book so we can see what  is goin on

thanks

Jim

• ###### 5. Re: Calculate difference between two LOD calculations when user selects "ALL"

Jim,

Attached is the Tableau Workbook.

When Type = Adds there are 6 Unique Employee IDs.

When Type = Terms there are 2 Unique Employee IDs.

When Type = Net it shows 8 Unique Employee IDs but it should be 6-2= 4.

It only works fine when the user does not select  "All" for the Product.

• ###### 6. Re: Calculate difference between two LOD calculations when user selects "ALL"

Ankit,

I added the Tableau workbook.  Can you take a look?

Thanks!

Wendy

• ###### 7. Re: Calculate difference between two LOD calculations when user selects "ALL"

Wendy,

I have fixed your Net By Role sheet and fixed few formulas . Now it looks good. Attached is the worksheet.

• ###### 8. Re: Calculate difference between two LOD calculations when user selects "ALL"

Thank you for all of your help but it doesn't work when I add in multiple health plans and/or multiple dimensions.  I plans on adding about 6 other dimensions to this so not sure how to handle this.  I attached the workbook.

• ###### 9. Re: Calculate difference between two LOD calculations when user selects "ALL"

Can you attach the workbook.

• ###### 10. Re: Calculate difference between two LOD calculations when user selects "ALL"

Here you go...

• ###### 11. Re: Calculate difference between two LOD calculations when user selects "ALL"

Wendy,

Don't add dimension to fixed LOD. Only keep Role in both calculation for term and adds.

{FIXED [Role]: countd(IF [Type] = 'Terms' then

[Employee  ID] else null end)}

Also change difference calculation as :

sum([Adds by Role]) - sum([Terms by Role])

It should work now.

Thanks,

Ankit Bansal

• ###### 12. Re: Calculate difference between two LOD calculations when user selects "ALL"

Thanks Ankit!  Everything works great now!!