12 Replies Latest reply on Nov 15, 2016 2:21 PM by Lynn Royce

    Same item in two different categories

    Robert Sinclair

      I've written a calculated field that breaks the contents of a field into groups.  I would like one of the items to be represented in two different groups, however when I  add it to one, it disappears from the other.

       

      if [Id Question] = 325622 OR [Id Question] = 325623 Then 'Checkout Employee Experience'

      elseif [Id Question] = 325613 then 'Overall Satisfied'

      elseif [Id Question] = 325613 OR [Id Question]= 325614 OR [Id Question] = 325615 then 'Loyalty'

      elseif [Id Question] = 325624 OR [Id Question]= 325625 Then 'Merchandise Experience'

      elseif [Id Question] = 325616 OR [Id Question] = 325617 OR [Id Question] = 325618 OR [Id Question] = 325619  Then 'Sales Floor Employee Experience'

      elseif [Id Question] = 325621 Then "Store Experience" else 'Other' end

       

      I want Item 325613 to be in both the "Overall Satisfied" and "Loyalty" categories. However it is only presented in the first instance (Overall Satisfied).  I assume that because it is already being addressed earlier in the calculation, it is omitted from Loyalty.  How can I work around this issue?

       

      Thanks

      RS

        • 1. Re: Same item in two different categories
          Ambili Suresh

          I think its a missing bracket

          Can you try this one

          if ( [Id Question] = 325622 OR [Id Question] = 325623) Then 'Checkout Employee Experience'

          elseif [Id Question] = 325613 then 'Overall Satisfied'

          elseif ([Id Question] = 325613 OR [Id Question]= 325614 OR [Id Question] = 325615) then 'Loyalty'

          elseif( [Id Question] = 325624 OR [Id Question]= 325625) Then 'Merchandise Experience'

          elseif ([Id Question] = 325616 OR [Id Question] = 325617 OR [Id Question] = 325618 OR [Id Question] = 325619 ) Then 'Sales Floor Employee Experience'

          elseif [Id Question] = 325621 Then "Store Experience" else 'Other' end

           

          Let me know if it work.

          • 2. Re: Same item in two different categories
            Jonathan Drummey

            I presume that Id Question is a field in your data source. In that case, what you're trying to do is take a record and make it be counted twice, within the same dimension, which Tableau won't do.

             

            Ambili, your revised solution won't work because as soon as [Id Question] = 325613 evaluates to True then the IF/THEN statement will exit with the resulting "Overall Satisfied", it won't evaluate that to True twice. (Robert, this is an example of why we often ask for packaged workbooks with sample data, if you'd provided that then Ambili could have tried it out and found that it wouldn't work.)

             

            In any case, there are various ways around this issue, here are the first two that come to mind:

             

            - Embed a record-level evaluation with a measure, then use multiple measures. Since we can put multiple measures in a single view, that can enable Tableau to count the same record multiple times. It seems like you're working with survey data, the measures might be something like an "Overall Satisfied" measure with the formula COUNTD(IF [Id Question] = 325613 THEN [RespondentID]  END) and a "Loyalty" measure with the formula COUNTD(IF Id Question] = 325613 OR [Id Question]= 325614 OR [Id Question] = 325615) THEN [RespondentID] END)

            - Create a table that maps the groupings to ID Questions, then join that to your data on the Id Question. This will give you the additional rows at the record level (and obviate the need for the calculation in the first place). You'll just need to be careful if you're counting responses per ID Question, since at least in the case of 325613 there would be 2x as many records as actual responses. That's where something like COUNTD(RespondentID) can be more accurate than SUM(Number of Records) or COUNT(Respondent), because it's counting respondents and not records.

             

            Jonathan

            1 of 1 people found this helpful
            • 3. Re: Same item in two different categories
              Robert Sinclair

              Jonathan -

               

              Thanks for the quick response.

               

              I wish to group (via calculated field) a single [ID_Qustion] into two groups to use as a filter.  I fail to see how using the CountD() function will allow me to achieve the groupings I'm after to use as a filter.

               

              My situation is more akin to Multiple/overlapping values in a case statement?

               

              Thanks,

              RS

              • 4. Re: Same item in two different categories
                Jonathan Drummey

                The way the multiple measures solution would work is that you'd put Measure Names on the Filters shelf and filter for the chosen measures. Unfortunately, that's not the greatest solution because the list would also show other Measures in your data, we have limited ability to filter the list.

                 

                Another option would be to use a parameter for the definition of the grouping, and then a single CASE statement like so:

                 

                COUNTD(

                CASE [myParam]

                     WHEN 'Checkout Employee Experience' THEN IF [Id Question] = 325622 OR [Id Question] = 325623 THEN [RespondentID] END

                    WHEN 'Overall Satisfied' THEN IF [Id Question] = 325613 THEN [RespondentID] END

                   WHEN 'Loyalty' THEN IF [Id Question] = 325613 OR [Id Question]= 325614 OR [Id Question] = 325615 THEN [RespondentID] END

                [..and so on...

                END)

                 

                And finally, either the cross product suggested in the thread you linked to or the join that I suggested (which can effectively net you the same results) would enable a regular Quick Filter to be used.

                 

                Now that I think about it, the parameter & CASE statement might be the way to go if you're ok with having a single-select for the parameter and the data isn't updating too often.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Same item in two different categories
                  Robert Sinclair

                  Okay, I'll give the CASE solution a try, but where is the [RespondentID] coming from.  You had that in your last solution and it is not a field that was in the original calculation I posted.  I don't get what it is referring to or it's role in the formula.

                   

                  Thanks RS

                  • 6. Re: Same item in two different categories
                    Jing Pan

                    Hi,

                     

                    Have you solved this question?

                    • 7. Re: Same item in two different categories
                      Robert Sinclair

                      Jan -

                       

                      Not really.  I ended up just duplicating the field under a different field

                      name. That allowed me to include the dimension within the same calculation

                      twice - not very elegant.

                       

                      Hope that helps.

                      rs

                       

                      On Tue, Apr 12, 2016 at 11:36 AM, Jing Pan <tableaucommunity@tableau.com>

                      • 8. Re: Same item in two different categories
                        Jing Pan

                        Yes, I did the same thing.... Hope we can figure out some way later

                        • 9. Re: Same item in two different categories
                          srilatha.kandala.0

                          Hi,

                           

                          I have a similar Requirement, tried duplicating the field .. it still dint work.

                           

                          Could you please let me know what exactly you have done ?

                           

                          Thanks...i appreciate your help

                          • 10. Re: Same item in two different categories
                            Robert Sinclair

                            Srilatha-

                             

                            Did you ever get an answer to your question?  If not, please post a example of what you are trying to do and I will see if I can help.

                             

                            Best,

                            Robert

                            • 11. Re: Same item in two different categories
                              Sommy Boucansaud

                              Hi All,

                               

                              Today I faced the same issue. I found a work around which work in some cases (where the resulting groups are nested for instance).

                               

                              Here the problem :

                               

                              In my source database, I had this kind of structure :

                               

                              TYPE     VALUE

                                  A           10

                                  B            5

                                  C            2

                               

                              What I wanted to get is a filter containing 2 values "Group 1" and "Group 2" where "Group 1" keep lines A & B and a "Group 2" keeps lines A,B & C.

                               

                              In order to get this done, I have created the following calculated measure [TYPE_NUMBER] :

                               

                              IF TYPE="A" THEN 0

                              ELSEIF TYPE="B" THEN 1

                              ELSE  TYPE="C" THEN 2

                               

                              Then, I have created a parameter [GROUPS] which will be my front filter which has those values :

                               

                              Parameter as Integer :

                              • 1 displayed as "Group 1"
                              • 2 displayed as "Group 2"

                               

                              Finally, I've created another calculated measure [TYPE_FILTER] which is a Boolean in order to keep the wished lines :

                               

                              [TYPE_NUMBER]<=[GROUPS]

                               

                              Once this is done, just applies the filter [TYPE_FILTER]=true , thus, when GROUPS is selected as Group 1, its value is 1 so only lines where TYPE_NUMBER<=1 (A & B) will be kept, if it is selected as Group 2, kept lines will be those where TYPE_NUMBER<=2 (A,B & C).

                               

                              This solution can be used for several cases, the key point is the boolean condition defined in the TYPE_FILTER measure.

                               

                              Hoping this helps.

                               

                              Sommy

                              • 12. Re: Same item in two different categories
                                Lynn Royce

                                Hi all,

                                For any others who find their way to this post...both of Jonathan Drummey's solutions work, depending on what your ultimate goal is.

                                I hacked the Superstore shipping mode to create this example of the first method (multiple measures) :