11 Replies Latest reply on Nov 11, 2013 6:39 AM by Ramon Martinez

    Hierarchical filtering

    Arjun Mehra

      Untitled.png

        • 1. Re: Hierarchical filtering
          Ramon Martinez

          Hi Arjun,

           

          The problem in your workbook is that you are filtering by Category, that is a child of Department according with hierarchy Product in your data set.

           

          To demonstrate the case, I created two new worksheets Sheet 3 and Sheet 4 and the Dashboards 2. You will see the order of filters in the dashboards as Department on Top of Category.

          If in filter Department we select Office Supplies the result will be as you expect, 451,990.

          Now, select (All) in Department and then select just Appliances in category, the result will be 121,651

           

          I hope this helps you to understand the hierarchy Product.

           

          Best,

          Ramon

          1 of 1 people found this helpful
          • 2. Re: Re: Hierarchical filtering
            Prashant Sharma

            Hi,

            I think you uploaded the wrong file. I do not think that this is simply doable. You can do something similar to the attached file.

             

            Warm Regards,

            Prashant Sharma - India | LinkedIn

            1 of 1 people found this helpful
            • 3. Re: Hierarchical filtering
              muthu.krishnan

              Hi Arjun,

               

              In your dashboards, it showing the correct result only. If you want to show only office supplies, then remove the sheet 2 from your dashboard.

               

              Regards,

              Muthu Krishnan. M

              • 4. Re: Hierarchical filtering
                Ramon Martinez

                Hi Prashant,

                 

                Thanks for the heads up. I've edited my last post uploading the correct workbook.

                 

                Regards,

                Ramon

                • 5. Re: Hierarchical filtering
                  Ramon Martinez

                  Hi Arjun,

                   

                  Have your review the workbook Book2_RM.twbx attached to my previous ports?

                   

                  Understanding the structure of the hierarchy Product in your data source will help to recognize the problem in your dashboard.

                   

                  Best

                  Ramon

                  • 6. Re: Re: Hierarchical filtering
                    Arjun Mehra

                    Hi Ramon,

                    I do understand the problem with my dashboard and the product hierarchy, but my client wants to drive the dashboard through a Category and see few metrics related to that category and compare it to the entire department(to which that category belongs).

                    One solution I could think of was to pre-aggregate all metrics at department level, but that would make the dashboard very heavy(duplicated metrics), so was looking for alternatives. Attached is my attempt.

                     

                    @Prashant - your suggestion is useful, thanks.

                     

                    Thanks

                    Arjun

                    • 7. Re: Hierarchical filtering
                      Ramon Martinez

                      Hi Arhjun,

                       

                      I think a better approach is to use a parameter with the list of categories (this parameter will be used as selector of the Category) and to create calculated fields for conditional display of metrics by Department which Category belongs to and for conditional display of metrics by selected Category. Do this make sense to you?

                      With this approach it is not required to create a secondary data source with aggregated metrics by Department

                       

                      I don't have time know but I'll try to implement this approach for you.

                       

                      Best,

                      Ramon

                      • 8. Re: Hierarchical filtering
                        Arjun Mehra

                        Hi Ramon

                         

                        The approach you suggest is exactly what I want to do but am unable to create a calculated field for it. Please post the formula if you are able to figure it out.

                        Also in my approach I have used only a single data source(Sheet1(Book1.xlsx)) in which I have pre-aggregated everything at department level and created duplicate columns/metrics (I know this is not the correct way, but the only way I can think of right now).

                         

                        Thanks

                        Arjun

                        • 9. Re: Re: Hierarchical filtering
                          Ramon Martinez

                          Hi Arjun

                           

                          I've implemented a solution to your requirement the following way:

                           

                          1.- Create a calculated field to concatenate Category and Department,I named it: Category Department concatenated

                          2.- Create a parameter based on the calculated field Category Department concatenated and name it Category Department concatenated parameter

                          3.- Edit alias of the parameter items to have only category part

                          4.- Create a worksheet to show metrics by Department

                          In Solution3_Department_view worksheet add Department to filter, select Tab Condition, By Formula

                           

                          [Department]=RIGHT([Category Department concatenated Parameter],LEN([Category Department concatenated Parameter])-FIND([Category Department concatenated Parameter],','))
                          

                           

                          Worksheet Solution3_Summary_Dept_view is similar to Department view but presenting summary metrics by department. This worksheet has also a filter by Department using the above formula

                           

                          5.- Create a worksheet to show metrics by Category. Similar approach to describe earlier but filtering by Category using the formula:

                           

                          [Category]=LEFT([Category Department concatenated Parameter],FIND([Category Department concatenated Parameter],',')-1)
                          

                           

                          Don't forget to show Parameter control to select by Category

                           

                          Create a dashboard with these views and the parameter Category Department concatenated parameter

                           

                          See the attached workbook with the solution. See how it works in Dashboard 2

                           

                          I hope this helps

                           

                          Best,

                          Ramon

                          • 10. Re: Re: Hierarchical filtering
                            Arjun Mehra

                            Hi Ramon,

                             

                            Thanks a lot for your time and effort. This actually helps.

                            Also I was just wondering if you know a way to change the alias through some list, because I have around 5000 categories and changing them all manually will take some time.

                             

                            Thanks,

                            Arjun

                            • 11. Re: Hierarchical filtering
                              Ramon Martinez

                              Hi Arjun,

                               

                              Yes, you can use the option Paste from Clipboard... in the Create Parameter dialogue:

                              1.- create an excel file

                              2.- in one column put values of the parameter

                              3.- in other column put values for alias

                              4.- select values in both columns and copy to clipboard (Ctrl + C key)

                              5.- click on button Paste from Clipboard

                               

                              I tested it and it works OK.

                               

                              Best

                              Ramon