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.

    Luke Tan
    Luke Tan Nov 21, 2016 12:39 AM

      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?

      • 326 Views
      • Categories: Calculations, Dates & Times
      • Tags:
        • 1. Re: calculating % of people who visit once a month for 12 months.
          Andrew Watson
          Andrew Watson Nov 21, 2016 1:51 AM (in response to Luke Tan)

          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.
            Luke Tan
            Luke Tan Nov 25, 2016 1:12 AM (in response to Luke Tan)

            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.
              Andrew Watson
              Andrew Watson Nov 25, 2016 3:03 AM (in response to Luke Tan)

              "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