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

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

    Wendy Weber

      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!!

       

      LOD Adds:

      {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]