2 Replies Latest reply on Jul 14, 2017 3:08 AM by Nicole Edmonds

    Using Exclude LOD to created a total showing Unexpected Results

    Nicole Edmonds

      HI All - I am creating a dashboard where I am needing to show for each month after a customer acquisition the ratio of volume divided by the total number of customers for that period. 


      I created 4 Calculations to accomplish this - 


      Customer Acquisition Date - to find the first order:

      {FIXED [Customer ID]: MIN([Created])}


      Repeat Order - to find orders after the first:

      iif([Created]>[Customer Acquisition Date],[Created],null)


      Months from Customer Acquisition:

      Datediff('month',[Customer Acquisition Date],[Repeat Order])


      And Fixed count customers - this to pull the total customers for the acquisition period regardless of the month from customer acquisition shown in the view. (I realize I am not using a FIXED LOD despite the name, that was where I started with this exercise

      sum({exclude [Month from Customer Acquisition]: countd([Customer ID (copy)])})


      What I am seeing is the total calculated with the EXCLUDE LOD calc isn't show the total I would expect:


      issue with total.PNG


      For the Fixed Count customers, I would expect this to be 6,138, as that is the sum of the customer count from the first column.  Any ideas?  I feel like I am missing something in the calculation, but haven't been able to figure out how to get the correct total from the calculation. Any ideas?  Packaged workbook attached, with a generic data set illustrating my problem.