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.