8 Replies Latest reply on Jul 11, 2018 6:55 AM by Ken Flerlage

# Counting Notes per Case by Customer - Help with Calculation

All,  I need some help with a calculation.

I'm trying calculate the Avg. Notes per Case for a customer

Example of Graph:

As you can see, I'd like to see the avg. notes per case by month, by customer.

I my data, I have two Dimensions I am using:

1. Count of All Cases Created: (COUNTD([Case Id]))

- For my specific Customer, I get 209, which I verified against the raw data, and is correct.

2. Note Count from Dealer  (This is an integer in my data)

-If I count this field, I get 3,392 (Below)

- If I Sum(Int(Note Count from Dealer)), I get  19,259

Looking at the data that feeds this, in Excel, if I do a Sum on count of notes, I get 695.  That 695 is the CORRECT number.  However, I cannot figure out why Tableau does not calculate that.  Can someone help me figure out what I'm doing wrong and how I can figure this out?

• ###### 1. Re: Counting Notes per Case by Customer - Help with Calculation

I'm confused. Where does 695 come from? Any chance of seeing some of your data? Also, would be helpful to see a workbook.

• ###### 2. Re: Counting Notes per Case by Customer - Help with Calculation

I attached the Excel document that shows what it is I am trying to calculate.  Again, if I do just a simple "SUM" in Excel, I get the correct Number.  However, in Tableau, I'm getting something completely different.  How can I calculate this correctly in Tableau?

• ###### 3. Re: Counting Notes per Case by Customer - Help with Calculation

Why are you counting the Note Count From Dealer field? If you Sum that instead, you should get 695.

My calcs are:

Count of All Cases Created

COUNTD([Case Id])

Average Notes

SUM([Note Count From Dealer])/[Count of All Cases Created]

See attached workbook.

• ###### 4. Re: Counting Notes per Case by Customer - Help with Calculation

Yes, I know. I am trying to sum the number in Tableau to get to that 695, but if I do, I get something like 19,000+, so I tried counting it to see if it made a difference.

I updated the calculation because the Note Count from Dealer is text:  SUM(int([Note Count From Dealer]))/[Count of All Cases Created]

The calculation I get is 19259 / 209 = 92.148...

• ###### 5. Re: Counting Notes per Case by Customer - Help with Calculation

Based on the data you provided, I was able to get to 695 by just summing the field. Are you working with a different data set? Are there some joins in your data that might cause the data to be duplicated?

• ###### 6. Re: Counting Notes per Case by Customer - Help with Calculation

If a data duplication problem is what you're running into (i.e. you are joining to another table which has multiple records for each case ID, thus each of these base records will show multiple times), then I could see how you'd run into this problem. You can use an LOD calculation to eliminate this problem. Do something like this to get the note count for each case:

Case Note Count

{FIXED [Case Id]: AVG([Note Count From Dealer])}

Then you should be able to Sum that to get to 695.

See the attached workbook and the sample data I mocked up.

1 of 1 people found this helpful
• ###### 7. Re: Counting Notes per Case by Customer - Help with Calculation

Thank you for your help! My issue was with the lines duplicating. I had a join on there, that joined to many records per case, like you mentioned.  The LOD function you gave me worked perfectly!