4 Replies Latest reply on Jul 3, 2018 4:35 AM by Zhouyi Zhang

# How to calculate the first value by category?

Hello! I have a specific question regarding customer retention rate by category (see attached): How do I create a calculated field which filled by the first total number of people by city and by pay type in tableau? How to define the function?

For example, I have 5 columns:

• first_week is the first week that people purchased our products.
• week_no is the week number that people purchased our products again.
• total_num is the total number of people that have purchased our products.
• pay_type is the type of payment (cash, or credit)
• city is the city that people finished order
In order to calculate the retention rate, I need to create a new field which is the number of people who bought our products by city and pay type for the first time. In the following, cal1 is the new field, and retention_rate is the total_num divided by cal1. I just wondering if there is a function to calculate cal1 in order to get the retention rate?

I really appreciate anyone can help!

current dataset is like this:

Thanks,

Simin

• ###### 1. Re: How to calculate the first value by category?

Hi, Simin

try below calculation for cal1

{Fixed [city],[first_week],[pay_type]:min(if [first_week] = [week_no] then [total_num] end)}

Please let me know if it works or not.

ZZ

1 of 1 people found this helpful
• ###### 2. Re: How to calculate the first value by category?

Good morning I think I understand

have you tried     {fixed  [city],[pay_type] : if Min( [week_no] )=min([first-week]) then sum([total_number])    }

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: How to calculate the first value by category?

This is workable. Thanks!

just need to change min to max:

{Fixed [city],[first_week],[pay_type]:max(if [first_week] = [week_no] then [total_num] end)}

Simin

• ###### 4. Re: How to calculate the first value by category?

Hi, Simin