10 Replies Latest reply on Sep 22, 2018 1:54 PM by Joe Oppelt

    Get a list of dimension values at a higher level of detail

    Ben Peterson

      This a timeline view of people who use the same IP address on the same date.

      cajun_navy.png

       

      I can get the count of people (Member Ids) with this LOD

      {FIXED [Transaction Type],[Transaction Sub Type],[Transaction Date],[Ip Address]: COUNT([Member Id])}

       

      and the maximum/maximum values of Member Id, i.e. for a given date

      {FIXED [Transaction Type],[Transaction Sub Type],[Transaction Date],[Ip Address]: MAX([Member Id])}

       

      That would cover instances where only 2 people share the same IP on the same date.

       

      How would I get a list of all members who share an IP address on the same date if more than 2? LOD expressions only work with aggregates, I've looked at Jonathan Drummey's creating a string list calculation (worksheet #15) in http://drawingwithnumbers.artisart.org/the-next-n-table-calculation/. Can't quite get it work for this.

        • 1. Re: Get a list of dimension values at a higher level of detail
          Joe Oppelt

          The approach you took is the way to go.

           

          Let me take a look.

          • 2. Re: Get a list of dimension values at a higher level of detail
            Joe Oppelt

            In the attached I made a new sheet.  If I understand correctly, you want a list of all member IDs that used a given IP on a given day.


            In sheet 2 the first thing I did was put a calc called INDEX on the sheet.  Whenever I play with table calcs, I start with INDEX.  When I get INDEX to behave the way I want, then I know how to set the table calc.

             

            I want 1-through-N for the list of members for a given day and a given IP.  Look at 10/11/2017 for a good example of that.  I want 1-through-4.  And with the right setting, I got it.  Right click the index pill and select edit table calc to see how I set that.

             

            Once I got that, I just have to set my [LIST] calc the same way.  Go to Sheet 2(2).

             

            There I added [list].  You can modify [list] to format the list the way you want, but that's how to use the previous_value function here.

             

            PS, there is really one more step.  You want to get the window_max of [list] to get the full list.  Or do

             

            if index = window_max(index) then [List] end

             

            or

             

             

            if LAST()=0 then [List] END

             

            Essentially you need to grab the last (or longest) value of [List].  And, of course, set the table calc settings to match [index].

            • 3. Re: Get a list of dimension values at a higher level of detail
              Ben Peterson

              Thanks Joe, I'll take a look. BTW, I've learned a **** of a lot from your popping and swapping techniques...which brings up a question: most popping seems to happen in the direction of top to bottom or right to left of the canvas, and those are the ways I've done it. Is it more difficult to pop from bottom to top or left to right?

              Ben

              • 4. Re: Get a list of dimension values at a higher level of detail
                Ben Peterson

                Joe:

                 

                I inadvertently left it out of the question description that I want to be able to display the list of members in the tooltip of the original sheet. Besides displaying a filtered version of your additional sheet in the tooltip, might there be another way?

                 

                Thanks again,

                Ben

                • 5. Re: Get a list of dimension values at a higher level of detail
                  Joe Oppelt

                  Ben Peterson wrote:

                   

                  ... Is it more difficult to pop from bottom to top or left to right?

                  Ben

                  I never found a way to do that.  I think Tableau just works in a left-to-right and top-to-bottom orientation.

                   

                  I get around it by popping things out from behind other things if I need something to pop out at the bottom or on the right side.  Still left-to-right and top-to-bottom, but I get the effect I need that way.

                  • 6. Re: Get a list of dimension values at a higher level of detail
                    Joe Oppelt

                    Ben Peterson wrote:

                     

                    Joe:

                     

                    I inadvertently left it out of the question description that I want to be able to display the list of members in the tooltip of the original sheet. Besides displaying a filtered version of your additional sheet in the tooltip, might there be another way?

                     

                    Thanks again,

                    Ben

                    For one thing you will need to have the Transaction Date on that sheet.  You make the list for a given day, correct?  If that's the case, the table for that sheet will need to know about Transaction Dates.

                     

                    And I see that member IDs have rows with multiple dates.  Your original sheet is partitioned by member ID first, so what do you want to do with members who don't share the same list of people on Day-2 as they have on Day-1?

                    • 7. Re: Get a list of dimension values at a higher level of detail
                      Ben Peterson

                      Hey Joe ( The Jimi Hendrix Experience - Hey Joe (Official Audio) - YouTube )

                       

                      Sorry about being unclear upfront. Each one of the marks represents a Shared IP instance, and each instance would have its own list of members, more than one instance can and does occur on a day. There is a Transaction Id in the Detail shelf. An instance is defined as a combination of Transaction Date, Transaction Type, Transaction Sub Type, and IP Address.

                       

                      Ben

                      • 8. Re: Get a list of dimension values at a higher level of detail
                        Joe Oppelt

                        I also need to put IP address on there, since the list is generated by IP Address.  When I did that, I needed to add IP Address to the Jitter calc to get the viz back to its original.  (I'm doing all the work in a copy of the original.)

                         

                        Next, I created a [Full List] calc.  It's grabbing the max value of all the [List] values generated in that calc.  It's at the bottom of tooltips.

                         

                        Look at the table calc settings for [Full List].  I had to set the two nested calcs to use all the dimensions and restart every IP.  But for the [Full List] calc itself I ended up excluding the Transaction ID dimension.  I'm not sure why, but with Transaction ID in the mix at that level it was duplicating the list string.

                         

                        I set up Sheet 2(2) so that it's pretty easy to find any mark from the copy of your original sheet to verify the values displayed in tooltips.

                         

                        Hey, can you test something for me?    I'm doing this work in Beta 2018.3.  I'm saving workbooks for you in 2018.1, but I also attached the same thing in 2018.3.  What version of Tableau are you on?  If not 2018.3, can you still open the 2018.3 on your version?

                        • 9. Re: Get a list of dimension values at a higher level of detail
                          Ben Peterson

                          Joe:

                           

                          I have version 2018.2 and can open the 2018.3 seamlessly.

                           

                          Thanks for helping, maybe I haven't been clear enough. Not sure if the calculations are returning all the members for a given transaction date-transaction sub type-ip address combination.

                           

                          Here's an example: on 11-16-2018, member 10001586 in on ip 192.168.25.75. Along with that member are 6 other members, a total of 7.

                          view data 7 members on 192.168.25.75.png

                          However, in the tooltip for member 10001586 on 11-16-2018, only a total of 4 member ids appear.

                          4 members tooltip for member 10001586 on 11-16-2017 on 192.168.25.75.png

                          Here are sheet 2 and sheet 2(2)

                          sheet 2.png

                          sheet 2(2).png

                          Personally, I'm leaning toward creating an additional sheet similar to sheet 2 to use a a viz in tooltip. First time I've tried this, and I've found the filter, when customized, a little quirky. The Tooltip Action FIlter doesn't always contain all of the dimensions I specify.

                           

                          <Sheet name="Shared Address History Timeline Viz in Tooltip" maxwidth="600" maxheight="600" filter="<Transaction Date>,<Transaction Type>,<Transaction Sub Type>,<Ip Address>">

                           

                          In the example above, sometimes only <Transaction Date> and <Transaction Type> are passed to the viz in tooltip sheet!?

                           

                          Ben

                          • 10. Re: Get a list of dimension values at a higher level of detail
                            Joe Oppelt

                            I added Transactn ID back into the table calc setting for Full List.  (New copy of the sheet.)  I don't know what I was seeing yesterday, but today it's the right thing to do.

                             

                             

                            The reason the viz-in-tooltip isn't working here is because the action puts a filter on the tooltip sheet, leaving only one Member ID on the sheet.  The table calcs need all the members on the sheet to string them together.

                             

                            Also, transaction type and sub-type aren't on Sheet 2 to receive a value from the primary sheet.