14 Replies Latest reply on Nov 15, 2017 5:19 PM by Zhouyi Zhang

# Using a filter on a view for a calculation

The sample workbook is attached.

What I am trying to do is calculate 'dollar churn' on the three accounts that paid in July 2016 vs. what they paid in July 2017.  I want to exclude the dollars that the fourth account paid, because they weren't a customer in July 2016.  I am using Tableau Version 10.4.

• ###### 1. Re: Using a filter on a view for a calculation

Hi, Jessica

How did you know the 4th account was not  a customer in July 2016, any business logic behind? because I saw the 2nd account also didn't pay anything in July 2016 as well.

ZZ

• ###### 2. Re: Using a filter on a view for a calculation

This is invoice data.  So I am assuming that if they didn't pay in July of 2016, they weren't a customer in July of 2016. There is no logic, other than the fact that no dollar amount is populated in July 2016.

So for this example month, the formula I am trying to get at is Customer Dollar Churn = (3,768+2,972)-(3,504+324+3,315)/(3,504+324+3,315) = 5.9%

I lost 5.9% of my Customer Dollars between the two months.

From a customer count standpoint: ((2-3)/3)=33%

THANK YOU for any help you can provide.

• ###### 3. Re: Using a filter on a view for a calculation

Hi, Jessica

I noticed that your formula to get the customer dollar churn includes "324" from account 2345, while I looked into the data source, account 2345 is exactly the same as account 7890, except the total amount value different. can you explain why account 2345 is included in the formula while 7890 isn't.

ZZ

• ###### 4. Re: Using a filter on a view for a calculation

I'm not sure I follow your question. The formula to calculate growth (or decline) of \$ churn is to look at the:

(recent amount total-old total)/old total

So my formula is taking the totals of those customers that are old vs. new, is just summing up those that are old or new paying customers. I think it is much easier to look at the screenshot I have attached. You can see what I am trying to get to:

(3,768+2,972)-(3,504+324+3,315)/(3,504+324+3,315) = 5.9%

• ###### 5. Re: Using a filter on a view for a calculation

Hi, Jessica

can you try below calculation field.

({ FIXED YEAR([Modified Invoice Date]):SUM( IF

DATEDIFF('year',

{ FIXED [Account]:MIN([Modified Invoice Date])}

,

{ FIXED [Account]:MAX([Modified Invoice Date])}

) >0 THEN

[Total Amount]

END

)

}

-

{ FIXED : SUM(

IF YEAR([Modified Invoice Date]) =  YEAR({ FIXED :MIN([Modified Invoice Date])}) THEN

[Total Amount] END

)}

)

/

{ FIXED : SUM(

IF YEAR([Modified Invoice Date]) =  YEAR({ FIXED :MIN([Modified Invoice Date])}) THEN

[Total Amount] END

)}

and below is the screenshot of result

Please let me know if you have question

ZZ

• ###### 6. Re: Using a filter on a view for a calculation

Hi -

Thank you for your response, I am a little confused on how to read this

formula. I feel like it is close but not quite what I was expecting because

it isn't including account #2345 (?). This is the formula that calculates

Churn:

((3,7682,972)-(3,5043243,315))/(3,504324+3,315) = 5.97%

*Note: Only those accounts that paid in July of 2016, should be included in

the calculation (I don't want to include Account #7890 which is considered

The question I am trying to answer: Of our 'original accounts' that paid in

July 2016, what is the dollar churn the following year? It should be 5.97%

of the dollars churned (or were lost).

THANK YOU for helping me with this - you are very close!

Jessica

On Tue, Nov 14, 2017 at 6:12 AM, Zhouyi Zhang <tableaucommunity@tableau.com>

• ###### 7. Re: Using a filter on a view for a calculation

Hi, Jessica

Sorry about the confusion.

I think the workbook you shared has different data for account 2345, that's why I have the make up for Invoice date.

I attached my workbook for your reference, hope this could make clear of what you are after.

ZZ

• ###### 8. Re: Using a filter on a view for a calculation

Hi...

Thank you - I am sorry but I don't see a workbook attached. And I logged in

to my case and didn't see an attachment posted there.

I dropped in your formula above, and am not coming up with the 5.6% (see

attached).

You are so helpful, and if I could see your workbook, that would probably

clear things up.  thank you again.

-Jessica

On Tue, Nov 14, 2017 at 8:20 PM, Zhouyi Zhang <tableaucommunity@tableau.com>

• ###### 9. Re: Using a filter on a view for a calculation

HI Jessica,

You cannot see attachment from inbox view.

Visit original post and find ZZ's attachment.

Shin

• ###### 10. Re: Using a filter on a view for a calculation

Hi,Jessica

you can find my attachment from the original thread, not the inbox.

Anyway, your workbook is almost there. Just need to add the filter to context as shown below

ZZ

• ###### 11. Re: Using a filter on a view for a calculation

Thanks, Shin

ZZ

• ###### 12. Re: Using a filter on a view for a calculation

NP

• ###### 13. Re: Using a filter on a view for a calculation

Thank you ZZ! You are a genius!  You nailed it! Thank you!

1 of 1 people found this helpful
• ###### 14. Re: Using a filter on a view for a calculation

Glad it works finally

Can you mark my answer as correct to close the thread?

ZZ