5 Replies Latest reply on Sep 18, 2014 9:18 AM by Matt Lutton

    count based on sum

    John Victor

      We have a situation where we track the Tools used by a User each Day, and how many times they used it. We also have records in the data for the day and tool combinations they didn't use anything, so basically we also have the zeros (nulls) in our data.

       

      What we need is a list of Users, and the number of Users, who have NOT used a Tool; with Day and Tool being filters (actions from a heat map).

       

      My idea would be to sum the number of times a User used a tool. Any User whose sum is 0 or null would then be considered a Non-User.  Even if the filter was for a particular Tool and time, or no filter was applied, this should work.  I could then list the Non-Users and do a COUNTD([Non-User]).

       

      But I don't know how to do a comparison with the sum of their Uses. If I try something like IF SUM([Uses]) = 0 or IF ISNULL(SUM([Uses])) I get an error about mixing aggregated and non-aggregated data.

       

      Is there a way to do this calculation?

       

       

      BTW, the reason I can't simply list users where [Uses] = 0 is that a User could have used a Tool this month, but not last month. Sothe Days they missed last month would show up with no Uses, and they'd mistakenly added to the Non-User list.  Similarly, if we were just looking at this month, and they hadn't used one of the tools, there would be an instance where their ID would have no Uses in the date, and they'd be mistakenly added to the list.

        • 1. Re: count based on sum
          Shawn Wallwork

          Got Sample Data?

          • 2. Re: count based on sum
            John Victor
            UserToolDayNumber of Uses
            AT19/1/20141
            AT29/1/20140
            AT39/1/20140
            AT49/1/20142
            BT19/1/20140
            BT29/1/20141
            BT39/1/20140
            BT49/1/20141
            CT19/1/20143
            CT29/1/20142
            CT39/1/20140
            CT49/1/20140
            AT19/2/20140
            AT29/2/20140
            AT39/2/20140
            AT49/2/20140
            BT19/2/20145
            BT29/2/20142
            BT39/2/20140
            BT49/2/20141
            CT19/2/20140
            CT29/2/20142
            CT39/2/20140
            CT49/2/20141

             

            If this is the data with no filters applied, then by summing the number of Uses by User, we see that no User has no Tool usage. Even though A has an entire day without usage, for the entire unfiltered time frame they used something on one of the days. So a count of users would be 0, and the list of non-users would be empty.

             

            But, if we filtered to 9/2, the sum of Uses by User would show that A has no usage. So the count would be 1, and the list of non-users would include A.

             

            Or, if we filtered by Tool, we'd see that for T2, User A is the only one who has no usage (despite User C having no usage for a single day).  Filtering on T3, we'd see that no one has usage during the two days, so the non-user list would have A, B, C, and D, and the count distinct would be 4.

             

            This is why summing is important because, otherwise, looking at one row at a time for instances of 0 Uses, all four Users would show up in the list just because there is a 0 associated to them somewhere in the dataset.  Expand this out to a whole year, and it would be like punishing a user because they missed a single day out of the entire 365.

            • 3. Re: count based on sum
              Matt Lutton

              Is this the RAW data structure, or what you are seeing inside Tableau?  If its your raw data structure, you can take this table of data, and copy/paste it directly into Tableau as a new data source, in order to post a sample packaged workbook with your view setup with the work you've completed here in the thread.  To attach, click "Use advanced editor" in the top-right corner, then the "Attach" option appears in the bottom right, next to @ Mention.

               

              For me, its very difficult to answer these questions without interacting with Tableau. Knowing what you are expecting to see as an output (mockup) can be helpful as well.  But there is always the chance someone else can answer with the information you've provided here.

               

              Cheers.

              • 4. Re: count based on sum
                John Victor

                Thanks for the tip.  I've whipped together a quick workbook.

                 

                 

                Output we'd expect to see:

                 

                With no filters applied (data as is), countd of users would be 0, list of non-users would be empty.

                 

                Filter on 9/2: countd of users would be 1, non-user list would be A.

                 

                Filter on T2: countd of users would be 1, non-user list would be A.

                 

                Filter on T3: countd of users would be 4, non-user list would be A, B, C, D

                 

                Filter on T3 and T4: countd of users would be 0, list of non-user would be empty.

                 

                 

                Hope this makes more sense now.

                • 5. Re: count based on sum
                  Matt Lutton

                  I'm sorry, but I really don't follow the results and "filter settings" you are describing.  But perhaps this will help--I was able to write a formula similar to the one you had mentioned previously, something like:

                   

                  IF SUM([Number of Uses]) == 0 or ISNULL(SUM([Number of Uses]))

                  then "Non User"

                  else "User"

                  end

                   

                  Does not throw an error; does that help you at all?  That's probably as far as I can take this without understanding more about the situations you are describing (there is no action filter from a heat map, for instance, and I'm just not following your logic in your filter scenarios)

                   

                  Cheers.