3 Replies Latest reply on Nov 25, 2016 3:03 AM by Andrew Watson

# calculating % of people who visit once a month for 12 months.

I have a set of logs that log when people come into a certain location. The log captures, name and date/time. The logs also include age of the person coming in.

How can I use tableau to calculate the % of people who come into the location at least once a month for 12 months (my logs spans 12 months of data) further segmented by age range?

The second part is easy, but how about first?

• ###### 1. Re: calculating % of people who visit once a month for 12 months.

For each name you could do a distinct count of the month. As you only have 12 months of data for someone to visit once a month the distinct count of months would be 12.

Your calculation could be something like:

{FIXED [Name] : COUNTD(MONTH[Date])}

To use that field a formula along the lines of this might do it (but test and modify as required), which will Null those names that haven't visited each of the 12 months, returning only those that have visited every month:

IF {FIXED [Name] : COUNTD(MONTH[Date])} = 12 THEN [Name] END

You should be able to countd all names and countd the above field to calculate the %.

• ###### 2. Re: calculating % of people who visit once a month for 12 months.

Thanks sorry but I realised what I needed to do is to actually calculate the average.

For each name - calculate number of times they came in at least once every month. So minimum is 1 , maximum is 12 call this unique monthly visits per year.

Then average the unique monthly visits per year for each group, where each group is people segmented by age.

• ###### 3. Re: calculating % of people who visit once a month for 12 months.

"For each name - calculate number of times they came in at least once every month. So minimum is 1 , maximum is 12 call this unique monthly visits per year."

This formula will do that: {FIXED [Name] : COUNTD(MONTH[Date])}

"Then average the unique monthly visits per year for each group, where each group is people segmented by age."

Perhaps this will do it for you? AVG({FIXED [Name] : COUNTD(MONTH[Date])})

1 of 1 people found this helpful