3 Replies Latest reply on Aug 14, 2012 3:38 PM by nickster

    calculating utilization rates

    nickster

      I am trying to calculate the percentage of total users that were active by day / hour and by user type. In the database I am connecting to I have a row for every transaction that a user makes with a date field for when that transaction was made. I have been trying to join this table with my users table (1 row per user active or not) and using the count distinct formula to get a distinct count of active user ids per day and a distinct count of all users per day. I am not getting the right results by going down this path and am starting to think about using custom sql to roll up active and inactive user counts by day and user_type

       

      Is custom sql the right path here or should I proceed with calculated fields in the existing tables I have?

       

      Any advice greatly appreciated

        • 1. Re: calculating utilization rates
          Joe Mako

          There are a few ways to accomplish what you are looking for. Please provide some sample data sets that represent the data structure and content you are connecting to. does not need to be real data, just something that represents the situations you are dealing with so the logic you want can be clear when you also provide what final result you want Tableau to produce for your small sample made up data.

          • 2. Re: calculating utilization rates
            nickster

            thanks Joe. I will get sample data to you shortly

            • 3. Re: calculating utilization rates
              nickster

              Attached is some sample data that represents the structure of the data I am working with. I am working with 2 tables - events and users. In the sample workbook I am working off a left join of users and events. However, I have also added the data sources independently as single tables and  an inner join as well in case useful

               

              I am trying to solve 2 problems

               

              1) I am trying to calculate the percentage of total users that have event type a and event type b transactions by week and day. I am able to calculate the cumulative number of users by registration date, but i am having trouble linking this to number of users by week and day that had an event transaction and getting a percentage for this.

               

              2) i am trying to group users into segments based on number of average number of event transactions per week. to calculate average event transactions i divide total events by tenure of that user and then write an if statement that groups users into different segments based on usage level. the problem I am running into is that the segment calculation is applied to every row of data in the joined table, and doesn't give me the result I want.

               

              Any help would be greatly appreciated.

               

              Thanks