1 2 Previous Next 18 Replies Latest reply on Jan 31, 2018 9:35 AM by Vikash Karra

    Calculated Field/Filter Help

    Vikash Karra

      This one seems a bit tricky. Please look at sheet 2. Right now, the filter is based on a course and the two values currently selected are How Businesses Obtain Credit and How businesses Obtain Credit Post.

       

      I'm interested in getting a count of users who have taken BOTH classes. So in this instance, it would be for User ID e2fc8b0c-6f8b-4fba-80b9-bf5919a44809, since they are the only User who has taken both classes that are in the filter.

      In addition, there is a Status column. You can be completed, in progress, or not started.

       

      Using that particular User ID above as an example, I would need to 'bucket'/create a new calculated field (lets call it New Status) for  each user depending on the current values in the 'Status' column

      You are complete if all of your courses have a status of complete.

      If you have taken a course that has a status of complete, and another one with

      a status of in progress, then you will have a New Status of In 'Progress.'

      If you have taken a course that has all statuses of Not Started, then you have a New

      Status of 'Not Started'

       

      So for user id e2fc8b0c-6f8b-4fba-80b9-bf5919a44809, the new status value should be In Progress (since they have 1 record for in progress, 1 for not started, and 1 for completed). Again, the user can take multiple courses. If he has a value of 'In Progress' in any of the statuses, then his New Status value should be 'In Progress'.

       

      Hope this makes sense as this seems a little challenging to do! Thanks

        • 1. Re: Calculated Field/Filter Help
          Joe Oppelt

          See attached.

           

          I created a LOD calc to tell me how many courses are on the sheet.  See [Courses Selected].  I display that in the title.

           

          Special note here:  Your filter shows many Course selections, but only two are applicable to the data in the underlying table because the  [Assignments Organization] filter only gets rows that have these two courses.  So if I select 4 of the courses, the LOD still only shows 2 selected because there are only 2 courses among all the data on the sheet.  To fix that, I changed the filter to "relevant values" as shown in this screen shot:

           

           

          If you were to set it back to "All values" and take the filter off to test this out, you will see the number in the title reflecting the number of courses selected.

           

          Anyway, right now there is a calc to tell me how many are selected  (Which is really how many are on the sheet.).  Then I created a calc to tell me how many courses a User ID has.  It's a lot like the [Courses selected] calc, except I do not EXCLUDE the [User ID] dimension.  Now I get a number of courses per ID.  See [Courses taken].  It's displayed on ROWS after the user ID.  So when these two calcs are equal, you have a user who is taking all the courses.

          • 2. Re: Calculated Field/Filter Help
            Vikash Karra

            got it. thanks joe (haven't had a chance to take a look at your solution, but will do so after my kids are asleep tonight!) so i guess if i put this in a dashboard, and have a filter for course, to get users (or a count of users) who have taken both courses, I'd create another calculation where courses selected = courses taken and filter where that is True. Any clue on the latter question? I.e.

            Using that particular User ID above as an example, I would need to 'bucket'/create a new calculated field (lets call it New Status) for  each user depending on the current values in the 'Status' column

            You are complete if all of your courses have a status of complete.

            If you have taken a course that has a status of complete, and another one with

            a status of in progress, then you will have a New Status of In 'Progress.'

            If you have taken a course that has all statuses of Not Started, then you have a New

            Status of 'Not Started'

            • 3. Re: Calculated Field/Filter Help
              Joe Oppelt

              Vikash Karra wrote:

               

              ... so i guess if i put this in a dashboard, and have a filter for course, to get users (or a count of users) who have taken both courses, I'd create another calculation where courses selected = courses taken and filter where that is True. Any clue on the latter question? I.e.

              ...

              That's exactly right.

               

              ...

              Using that particular User ID above as an example, I would need to 'bucket'/create a new calculated field (lets call it New Status) for each user depending on the current values in the 'Status' column

              You are complete if all of your courses have a status of complete.

              If you have taken a course that has a status of complete, and another one with

              a status of in progress, then you will have a New Status of In 'Progress.'

              If you have taken a course that has all statuses of Not Started, then you have a New

              Status of 'Not Started'

              You can do a ton of stuff with this.  Yes.

               

              You can make all sorts of LODs, and depending on whether you want the filters to impact the LODs, you would either use INCLUDE/EXCLUDE (to take into account the filtering on the sheet) or FIXED (to evaluate data independent of what gets filtered on the sheet.)

               

              I find FIXED a whole lot easier to use, but sometimes you only want the data on the sheet to be evaluated, not all rows for the user as they are found in the data source.

               

              I'll log in from time to time this weekend, so if you have questions just ask.  I'll help.

              • 4. Re: Calculated Field/Filter Help
                Vikash Karra

                Hey Joe! Thanks for the help again. I def need to research more about the include/exclude features. Last ? is this:

                 

                I used the logic you helped me to build the combined courses and learners worksheet. I'm stuck at this:

                 

                If you look at Sheet 9 for example: you can see the users that have taken BOTH classes. Right now, using dummy data, How Businesses obtain Credit had 2 people take it, and How businesses Obtain Credit Post had 2 people take it. Ideally, a bar graph that has the course name with the # of users that have taken it is what I'm after. Perhaps it's my lack of knowledge about include/exclude, but I cannot figure out how to come up with 2 users for each course. I keep getting 11, etc. Any help would be appreciated. Thanks again!

                • 5. Re: Calculated Field/Filter Help
                  Vikash Karra

                  One last thing (and I'm looking at how to do this, so if i get it done, then I'll shoot ya a msg!). If you choose org ID

                  6f7a18d0-1727-4dbd-a02c-b41137ea144c, it displays 3 bar graphs for Not Started. It should display 1 bar graph for each status (if there is a status that is). So in this case, not started should be displayed once, with a count of 3 users. I know it's an error with probably one of my calculations, but can't figure out which one. Thank you sir!

                  • 6. Re: Calculated Field/Filter Help
                    Vikash Karra

                    Ok I think i solved the msg above . Only thing I would need help figuring out is still this:

                     

                    I used the logic you helped me to build the combined courses and learners worksheet. I'm stuck at this:

                     

                    If you look at Sheet 9 for example: you can see the users that have taken BOTH classes. Right now, using dummy data, How Businesses obtain Credit had 2 people take it, and How businesses Obtain Credit Post had 2 people take it. Ideally, a bar graph that has the course name with the # of users that have taken it is what I'm after. Perhaps it's my lack of knowledge about include/exclude, but I cannot figure out how to come up with 2 users for each course. I keep getting 11, etc. Any help would be appreciated. Thanks again!

                    • 7. Re: Calculated Field/Filter Help
                      Vikash Karra

                      Joe - I might bug you some more btw. I'm stuck like Chuck with some of the issues above and might have some more (as what you have showed me on Friday has been great), but some new business logic might come into play and wasn't sure how to handle that! Again, appreciate the help!

                      • 8. Re: Calculated Field/Filter Help
                        Vikash Karra

                        Hey Joe - sorry, I kept on adding more ?'s and I was replying to myself. If you get any time, would def probably need a little more help!

                        • 9. Re: Calculated Field/Filter Help
                          Joe Oppelt

                          When you try to add to a sheet one of these: [Calculation for Courses Completed], [Courses Selected], [Courses Taken] ... you'll notice that they go onto the sheet at AGG([field name]) instead of SUM([field name]).  This tells you that Tableau is treating these as aggregates, not as row-level measures.


                          The way to further-aggregate aggregates is to use table calcs.  (You can't do SUM(SUM([Sales])), for instance.  You have to do WINDOW_SUM(SUM[Sales])).  the easiest way to demonstrate this is to make a calc that says SUM([Total Units]).  Put that on a sheet.  You'll see that Tableau puts it on as AGG([that calc]), not SUM([that calc]).  And if you wanted to find a total of [That calc], you would not be able to do SUM[That calc]).  You would have to do WINDOW_SUM([That calc]) to get it to work.)

                           

                          On sheet 10 I simulated Sheet 9, but made a calc that gives you a total of users that have [Calculation for Courses...] = TRUE.

                           

                          I'm not sure what you need that for though.  The number is always going to be the same for all courses by the way we've filtered this sheet.  But there it is.

                          • 10. Re: Calculated Field/Filter Help
                            Vikash Karra

                            Joe - thanks man. I've learned a ton from you (gonna try to test this out btw). If i ever meet ya, I'll owe ya a lunch! You have my gratitude. Any links you suggest reading to get more advanced with Excludes/Include calculations and when to use?

                            • 11. Re: Calculated Field/Filter Help
                              Vikash Karra

                              Hey Joe - if you take a look at Sheet 10, for course 3PG, it brings back 3 users for that course and each user's status. There are 9 total records for that course. But it should be 4 completed, 2 Not Started, and 3 In progress (see audit worksheet). I created another worksheet, Audit Update as to what I'd want to see, but I'd like to use that calculation you used as I'm not sure if what I did will hold up if a user tries selecting multiple courses that a user has taken. The courses selected vs taken I think it still working (i started using more dummy data to see if how it functions with the tips you gave).

                               

                              Also, on Worksheet Audit, I think the logic works. I.E if you select another course in addition to 3PG, i.e. Content CC Only nothing shows up (which is great, because there are no users who have taken BOTH courses). However, on Sheet 10, that logic doesn't work. It still shows the overall user count (9) for course 3PG (it should be blank, as no users have taken 3PG and Content CC Only.

                               

                              Thanks again! This has been a tricky dashboard (at least to me).

                              • 12. Re: Calculated Field/Filter Help
                                Vikash Karra

                                Disregard. Think I figured it out. Thanks again for the help Joe.

                                • 13. Re: Calculated Field/Filter Help
                                  Joe Oppelt

                                  Vikash Karra wrote:

                                   

                                  Joe - thanks man. I've learned a ton from you (gonna try to test this out btw). If i ever meet ya, I'll owe ya a lunch! You have my gratitude. Any links you suggest reading to get more advanced with Excludes/Include calculations and when to use?

                                  How I learned this (and am still learning it!):  Following, helping, and asking threads on this board.  I haven't found a better learning method than playing in here! 

                                   

                                  I started playing on this board almost 4 years ago when I started using Tableau.  Turns out the first thing I was trying to do involved complicated table calcs, and people jumped in and walked me through the intricacies of what I needed to do.  Right then and there I decided I wanted to get good enough to help out someone else the same way some day.  Now I do.

                                   

                                  Second best method:  Going to as many breakout sessions as possible at the Tableau conference they have each year.  I've been to 4 conferences now.  And I'll be going this year as well.  (New Orleans, late October.)  In between keynote speakers and events they have HUNDREDS of sessions on anything and everything you can imagine regarding Tableau.  And my favorites are always the ones given by experts (usually from Tableau, but also Zen Masters from all over the world) about the tougher parts of messing with Tableau.  Table calcs.  LOD calcs.  When it's best to use LODs over table calcs and vice versa.  Tricky uses of filter actions, url actions, etc.  Jedi-level tricks.  Session after session.  Some are hands-on, and some are lecture style.  You'll get to take a dozen of these if you want to pack your days.  (And you should.)

                                   

                                  And if you go, I'll let you buy me breakfast at the free breakfast buffets they do each morning.  (PS:  They also include free lunch each day, and most days dinner is also included in the price of registration.  So maybe I'll let you buy me meals all day!  )

                                  • 14. Re: Calculated Field/Filter Help
                                    Vikash Karra

                                    Hey Joe! haha thanks for the response. Had 1 last thing which i might need assistance on. Hoping this makes sense!

                                     

                                    I've created a dashboard. It has a Combined worksheet (which you've helped me on!). Functionality is that it only brings back metrics for users who are in all of the courses selected.

                                     

                                    You'll see a Progress Detail worksheet as well. This brings back the assignments within the courses for all users who are in all of the courses that are selected.

                                     

                                    I've created a worksheet called Complete/Not Complete (which is where I'm having issues). Basically, if you are complete, then you are in the complete bucket, else you are not complete (so if you are in progress, not started, past due, then in essence you are not complete).

                                     

                                    My Pie chart brings back the right #s when you select 1 course. However, when selecting two courses, it doesn't seem to be jiving. I.E. if you selet How Businesses Obtain Credit and How Businesses Use Credit, it doesn't seem to be filtering correctly. It should basically bring back a Pie Chart that has a value of 8 for not Complete (i.e. you if you add in progress, not stated, past due) from the graph below the pie chart, that should give you a total of 8. (or you can see the audit worksheet). I'm pretty sure that the pie chart is messing up because of user id, but not sure how to resolve   Obviously if I select a multiple courses that don't have the same user taking them, the pie chart shouldn't appear (similar to how the other worksheets were built). Man thanks again!!!

                                    1 2 Previous Next