Tableau Calculated Field using FIXED

Question

I have a database in Tableau from an Excel file. Every row in the database is one ticket (assigned to an Id of a customer) for a different theme park across two years.

The structure is like the following:

Every Id can buy tickets for different parks (or same park several times), also in different years.

What I am not able to do is flagging those customers who have been in the same park in two different years (in the example, customer 004 has been to the park a in 2016 and 2017).

How do I create this calculated field in Tableau?

(I managed to solve this in Excel with a sumproduct fucntion, but the database has more than 500k rows and after a while it crashes / plus I want to use a calculated field in case I update the excel file with a new park or a new year)

Ideally, the structure of the output I thought should be like the following (but I am open to different views, as long I get to the result): flag with 1 those customers who have visited the same park in two different years.

Answer 1

• Create a calculated field called customer_park_years =

{ fixed [Customerid], [Park] : countd([year]) }

• You can use that on the filter shelf to only include data for customer_park_years >= 2

• Then you will be able to visualize only the data related to those customers visiting specific parks that they visited in multiple years. If you also want to then look at their behavior at other parks, you'll have to adjust your approach instead of just simply filtering out the other data. Changes depend on the details of your question.

• But to answer your specific question, this should be an easy way to go.

• Make a note that countd() can be slow for very large data sets, but it makes answering questions without reshaping your data easy, so it is often a good tradeoff.