11 Replies Latest reply on May 27, 2014 1:06 AM by Jonathan Drummey

    Parent Total to be constant and not depend on child

    Tina Matt

      Hello,

       

      In the attached workbook, i am getting Department Total and corresponding Category numbers.

       

      I would like to get the total of the parent always to be visible. I know we could combine it on a dashboard. But is there anyother way we can get this in a single sheet itself. The reason, why i am asking this is, I have close to 50 metrics over a period of 2 years of time frame. Dashboard way of combining the total is having formatting issues..

       

      Basically, can we get this:

       

      Parent :

      Child 1:

      Child 2

      Child 3:

       

      i have to give user option to select child. Irrespective of selection of Child metric, I want the Parent number to be constant.

       

      Can you please help.

       

      Thanks,

      Tina.

       

      EDIT: modified the attachment

        • 1. Re: Parent Total to be constant and not depend on child
          Pedro Machado

          Hi Tina,

           

          Is this what you want? I created a new sheet that shows subtotals and implements a table calculation filter on the category field.

           

          This filter will be applied after the total is calculated so you can show/hide categories without affecting the department total.

           

          The formula I used is:

           

          Category Filter = lookup(attr([Category]),0)

           

          You can use max() or min() instead of ATTR(), but you need an aggregate in order for this to work.

           

          Note this solution could be slow if your query is returning too many marks. The actual total calculation is being done in Tableau and not in the source.

           

          Let me know if this helps.

           

          Pedro

          • 2. Re: Parent Total to be constant and not depend on child
            Tina Matt

            Great.. it is exactly what i am looking for. Thanks for your time.

             

            one quick question, can we get it as the first column and then can we have the child dimensions.

             

            also, how can we get it in the source it self. any idea on how we could modify the sql.

             

            Thanks again..

             

            Regards,

            Tina.

            • 3. Re: Parent Total to be constant and not depend on child
              Pedro Machado

              Tina,

               

              Could you clarify what you mean by ...

               

              can we get it as the first column and then can we have the child dimensions.


              and


              how can we get it in the source it self. any idea on how we could modify the sql?


              If you are asking whether you can add the calculation to the SQL, the answer is yes, but I think that would just complicate things.


              I'll be able to give you more specifics after I understand your questions better.


              Thanks,


              Pedro

              • 4. Re: Parent Total to be constant and not depend on child
                Tina Matt

                Sure.

                 

                In the attached workbook, we have the total as the last column.

                 

                what i trying to ask is.. if we can get this as the first column before category.

                 

                hopefully, i was clear. please let me know if you have any questions.

                • 5. Re: Parent Total to be constant and not depend on child
                  Pedro Machado

                  Tina,

                   

                  I don't know of a way to move the location of the totals in Tableau. Have you tried searching the forums for this?

                   

                  If you have access to the database, you could write a view that has a "summary level" column. This column would indicate if the record represents a category level or a department level summary. You could then rearrange the different values of the "category" to show totals to the left.

                   

                  If your database supports this syntax, you can use GROUP BY Department, ROLLUP(Category). This will calculate a summary record for each category/department combination and one for each department (all categories).

                   

                  Alternatively, you can create a couple of GROUP BY queries and UNION them to pre-calculate all the summaries you need:

                   

                  SELECT 'Category' as summary_level, department, category, sum(Sales) as Sales

                  FROM table

                  GROUP BY 1,2,3

                  UNION ALL

                  SELECT 'Department' as summary_level, department, 'All' as category, sum(Sales) as Sales

                  FROM table

                  GROUP BY 1,2,3

                   

                  Although you can do this with custom SQL from Tableau, I find the performance is better if you use a DB table or view. Tableau seems to want to run the custom SQL every time you change something on the worksheet.

                   

                  I am sorry I can't give you an easier answer. Maybe other people have other ideas.

                   

                  Pedro

                  • 6. Re: Parent Total to be constant and not depend on child
                    Tina Matt

                    Pedro Machado

                     

                    I have added functionality to this solution now.

                     

                    I have multiple sheets which uses this Category Filter. Category Filter = lookup(attr([Category]),0)'

                     

                    and on a dashboard i have all these sheets. am i not able to make the above filter global. any idea, why ?

                    • 7. Re: Parent Total to be constant and not depend on child
                      Tina Matt

                      Also, when we do show quick filter for Category Filter, we get an extra '*' value.

                       

                      any idea why is it so?

                      • 8. Re: Parent Total to be constant and not depend on child
                        Pedro Machado

                        I believe the table calculation fields are local to the view and I don't think there is a way to make a global filter using a table calculation. Let me know if this is a big deal and we can try to ping someone else to come look at this thread and hopefully offer some suggestions.

                         

                        The * shows because we are using ATTR(). You can search this forum for attr() and learn more about it.

                         

                        To avoid the *, you can use max() or min() instead of attr() in the calculated field formula.

                         

                        Pedro

                        • 9. Re: Parent Total to be constant and not depend on child
                          Tina Matt

                          thank you, the * is not visible now.

                           

                          What i am trying to do is a number of charts with parent and child metrics and then combine them on a dashboard. i want both the parent level trend and the corresponding child level trends with out compromising on the parent level metrics, meaning parent level metrics should always remain the same irrespective of the child selections.

                           

                          the method that you gave perfectly solves this problem. now, it is required for me to extend it to other charts which are on the dashboard.

                           

                          it is required for me.. can you please check to see if there is any way we can extend it on a dashboard.

                           

                          • 10. Re: Parent Total to be constant and not depend on child
                            Pedro Machado

                            Jonathan Drummey any ideas to make a table calc filter global? The only option that comes to mind here is calculating the totals in the DB and using a regular filter like I mentioned in one of my earlier messages.

                            Thanks,

                            Pedro

                            • 11. Re: Parent Total to be constant and not depend on child
                              Jonathan Drummey

                              Hi Pedro,

                               

                              Only dimension & regular aggregate filters can be scoped or global filters. And only dimensions can be the origin & target of Filter Actions. The only way I know of to make table calc filters “global” is to use a parameter and then make table calc filters in each worksheet responsive to the parameter.

                               

                              However, there’s an alternative route that might work in this case. Use a duplicated data source, and turn off any linking dimension(s) that you want to filter in the primary but not the secondary. Then you can use the filtered measure from the primary and the non-filtered measure from the secondary. I don’t have a workbook handy nor time to put one together right now, hopefully that makes sense!

                               

                              Jonathan