6 Replies Latest reply on Dec 4, 2012 5:49 PM by rozvin.marchan

    How to count a row in two groups?

    rozvin.marchan

      Table Definition:

      ItemName, Quantity

       

      Sample Data:

      Item1, 3

      Item2, 4

      Item12, 6

      Item3, 6

       

      I created calculated field to create the group name but I do not know on how to count the Quantity in two different groups.

       

      Sample output of expected behavior:

       

      Group Name, Quantity

      GroupName1, 3

      GroupName2, 13

      GroupName3, 6

       

      Basically, GroupName2 is total of Item1, Item2 and Item12.

       

      Could you enlighten me on this special case?

        • 1. Re: How to count a row in two groups?
          Catherine Rivier

          Hi,

          If I'm reading your requirements above right, it seems like each item can be in multiple groups (so Item1 is in Group1 and Group2, but Item2 is only in Group2)?  It would help if you could describe these logic rules you have.  Would Item123 be counted in Group1, Group2, AND Group3?

           

          The answer will likely involve creating a few different variables, rather than one single variable, that combine items as you want - Group1 has sums if only Item1, Group2 sums if Item1, Item2, or Item12, Group3 sums if Item3, etc..  Essentially, you have to go with multiple variables if you're "double counting" several rows.

           

          If this creates issues, you might want to tackle this with a Union query in your data source, which will allow you to duplicate rows that need to be counted in multiple groups.  This will let you have all groups in one variable.

          1 of 1 people found this helpful
          • 2. Re: How to count a row in two groups?
            rozvin.marchan

            I have very few ItemName values.

             

            Item1, Item2, Item3, Item4, Item5

             

            Group1: Item1, Item2, Item3

            Group2: Item1, Item2, Item3

            Group3: Item1, Item2, Item3, Item4

             

            Item5 is not belong to any group.

             

            I could determine it by ItemName because I have very few possible values.

             

            Your suggestion about creating variables is sounds interesting.

            Could you guide me on how to do it?

            How is the performance of the two suggestions?

            Is there a chance to achieve it via Sets? like create sets to group it then combine.

            • 3. Re: How to count a row in two groups?
              Catherine Rivier

              OK great, thanks for the detail.

               

              Attached is a solution with the variable solution.  I created the test data which just has fields ITEM and SALES.  For every Measure you want to summarize by groups, you'll want to create one new calculated field per group.

               

              For example, Group1Sales is:

              IF [ITEM]='Item1' THEN [SALES]

              ELSEIF [ITEM]='Item2' THEN [SALES]

              ELSEIF [ITEM]='Item3' THEN [SALES]

              END

               

              Group2Sales is:

              IF [ITEM]='Item4' THEN [SALES]

              ELSEIF [ITEM]='Item2' THEN [SALES]

              ELSEIF [ITEM]='Item3' THEN [SALES]

              END

               

              Group3Sales is:

              IF [ITEM]='Item1' THEN [SALES]

              ELSEIF [ITEM]='Item2' THEN [SALES]

              ELSEIF [ITEM]='Item3' THEN [SALES]

              ELSEIF [ITEM]='Item4' THEN [SALES]

              END

               

              You can repeat this as needed for any numeric field you want to use in your data set, and it will only count the numbers for that group.

               

              Is there a chance to achieve it via Sets? like create sets to group it then combine.

              As for Sets, I believe that would only work if each view was unique - that is on a single view, you were only looking at 1 group at a time.  I'm guessing you want to do comparisons?   (You can try this in the data - you'll see if you create sets this way, they are actually filters, so you can't see every group at the same time.)

               

              How is the performance of the two suggestions?

              Performance issues you'll likely notice are just flexibility.

               

              Creating the variables and leaving your data source as is:  You may notice some views are more challenging to to create, you may not have flexibility with things like table calculations.  But it all depends on what you want to see from the data.  On the positive side, you'll find this is very flexible if you ever want to make changes, like if groups change.

               

              If you adjust the data source with a union query, by which I mean changing this:

              ITEMSALES
              Item110
              Item113

              to this:

              ITEMSALESGROUP
              Item110Group1
              Item113Group1
              Item110Group3
              Item113Group3
              Item110All
              Item113All

               

              The main issue is coming up with the logic to correctly do this and maintain the integrity of your data.  And any future changes to groups would be very difficult.  You'd always have to be careful using any totals, because you'd be repeating records (hence the All group).  You'll also be tripling or quadrupling the total rows, which is a big problem if you have a lot of data.

              But on the positive side, you'll very easily be able to compare Group totals.

               

              It all depends on what you're trying to see!   But hopefully this helps get you started - let me know if you have other questions.

              • 4. Re: How to count a row in two groups?
                rozvin.marchan

                That is very helpful and almost the answer.

                 

                Is it possible to have only one field for the Group because now, I am getting different field. So my report has different group fields on each group.

                 

                Here is what I would like to have:

                 

                                        | 01/2011                                      | 01/2010

                Groups Column | Sales     | Records | Growth Sales | Sales     | Records | Growth Sales

                Group1 Sales    | 301.0     | 33.0       | xx%              | xx         | xx          | xx%

                Group2 Sales    | 293.0     | 36.0       | xx%              | xx         | xx          | xx%

                Group3 Sales    | 366.0     | 46.0       | xx%              | xx         | xx          | xx%

                 

                If you have notice, we grouped and compare the result of previous period.

                 

                Your help is highly appreciated.

                • 5. Re: How to count a row in two groups?
                  Catherine Rivier

                  So unfortunately, this goes back to the restrictions on how your data is originally set up.  As is, you really can't combine the 3 GroupSales variables into one.  It basically comes down to Tableau not really thinking of a row multiple times within the same variable.

                   

                  But, with most things Tableau, there are several workarounds.

                   

                  SOLUTION #1:   You can create several different sheets with your data and "stack" them within a dashboard.  See the attached workbook for details.  Dashboard HORIZONTAL STACK DASHBOARD allows you to combine each sheet horizontally, so you'll have one sheet per variable.  Dashboard VERTICAL STACK DASHBOARD has one sheet per group.  It takes some formatting to get things to look like you want, like hiding titles and fitting the sheet to width (or height), but if you can't change the data source, this is an effective way to get things done.

                   

                  SOLUTION #2:  This goes back to the Union query of your data.  I don't think you've said what format your data comes in.  If SQL, it's a pretty straightforward custom SQL using UNION ALL.  (Let me know if you need more info on writing a SQL statement like that.)  If Excel, it's a little more manual, which may be difficult, though I imagine there's some kind of workaround for that.  But basically, as I show above, you want to make sure that each record for Item1 is repeated once for Group1, and once for Group2.  And for Item 2, repeated for Groups 1, 2, and 3.

                  I created an example for this data - it's new Data Source UnionData, and results are in dashboard WITH UNION-IZED DATA.  You can see this now does exactly what you need, but like I said, the work for this is all in setting up the data like you need beforehand.

                   

                   

                  I believe these are your only options, but like everything here, someone might have a different/better solution I'm missing?

                  But, I hope this helps!

                  1 of 1 people found this helpful
                  • 6. Re: How to count a row in two groups?
                    rozvin.marchan

                    Thank you very much.

                     

                    We opted to use UNION ALL because of the formatting issue.

                    All your replies are correct answer. Let me to choose the first one.

                     

                    We highly appreciate your help.