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

# Using Exclude LOD to created a total showing Unexpected Results

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:

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.

• ###### 1. Re: Using Exclude LOD to created a total showing Unexpected Results

Hi Nicole

I'm not sure your calc is wrong...On a separate sheet if I just count distinct customers by quarter of acquisition date, I get the 4923 figure...

Not sure where the 6138 is coming from at the moment...

Donna

• ###### 2. Re: Using Exclude LOD to created a total showing Unexpected Results

Ok - so this was probably just a case of another set of eyes needed - You're right, the number is correct as expected, so the grand total would be the problem in the table I provided.  The 4,923 should be the number I need.  Thanks!