7 Replies Latest reply on Feb 14, 2017 10:35 AM by Srinidhi Narayan

    How to create drill-down Actions on bar charts with multiple data sources

    Jaskaran Dhillon

      Hi all,

       

      I want to create a drill down action on my 2017 bars (respectively on 250-499k, 500-999k, and 1M+). I have the data i want to drill them down to on the "Data" sheet. However I am not being able to set up an action on "select" to try to show that data when the user double clicks respectively on each of the 3 bars of 2017. I am attaching my workbook, so any ideas are welcome. The following is where I am stuck:

      Moreover, the bar charts are coming from a different data source and the data I want to show is coming from a different data source. Therefore, in my packaged workbook I have 2 different data sources. I want to filter out the drill down data based on "category" (250-499k, 500-999k, 1M+)

      Any ideas are welcome.

      action drill down.PNG

       

       

       

       

       

       

      I want "run action on" [select] for all the 3 bars for 2017. Any help would be appreciated.

       

      Cheers,

       

      Jaskaran

        • 1. Re: How to create drill-down Actions on bar charts with multiple data sources
          Srinidhi Narayan

          Jaskaran,

           

          Is it possible to get the data for your AUM sheet in a non aggregated manner?  Because of how your data is shaped and pre-aggregated, what you ask is not going to be trivial.     Since your "categories" are actually separate measures in your AUM sheet, you cant essentially use that as part of your filter action.   You need a dimension that can be mapped across the two data sources in order to achieve what you need.

           

          And another  question - what dimension in your detail data matches up to the Year in the summary?   You will need that dimension in addition to reshaping the data to make the networth categories as a separate dimension.  

          • 2. Re: How to create drill-down Actions on bar charts with multiple data sources
            Jaskaran Dhillon

            Hi Srinidhi,

             

            I appreciate your input. This way I am trying to get the aggregate AUM data into non-aggreggate. Once I achieve that I will keep you posted and ask further.

             

            Again thanks for your input,

             

            Jaskaran

            • 3. Re: How to create drill-down Actions on bar charts with multiple data sources
              Srinidhi Narayan

              I looked into your workbook a little bit more ... I believe you essentially want both the Year and the net worth categories to be filters.  In order for that to happen, you need to reshape your data to have net worth category as a dimension.  Additionally - you want to match the Category names across both datasets.  You have different syntax for this across the two datasets.

               

              That still leaves us with the challenge of matching up the right dates in the rows in your detail to the right "years" in your summary.  This may get tricky.    ALthough, I am now wondering why you need the summary info to begin with? Cant that info be aggregated from the detail? or are you validating data across two sources?

              • 4. Re: How to create drill-down Actions on bar charts with multiple data sources
                Jaskaran Dhillon

                Okay so I changed the syntax of the category in both data sets to match. Moreover, I believe that "category" is a dimension calculated field in the data sheet.

                 

                Regarding the filters, you are right however regarding the year filter, I only need the drill down action on 2017, however as you mentioned it would be according to net worth category.

                 

                I need that detail info for the end user to see in detail who holds certain accounts and how much they hold in that particular account.  Therefore, back to your question I believe I am validating data across both sources since I want the end user to get that detailed information once they double click those bars.

                 

                Any suggestions?

                 

                Thank you

                • 5. Re: How to create drill-down Actions on bar charts with multiple data sources
                  Srinidhi Narayan

                  Did you reshape the data ?  By Reshaping - I mean pivoting the categories and account counts to be on its own row.  See attached.  This makes sure that your categories are a dimension by itself, and avoids the use of "measure names".

                  • 6. Re: How to create drill-down Actions on bar charts with multiple data sources
                    Jaskaran Dhillon

                    So by doing this will I be able to crate a drill down action on the 2017 bar charts to view the required data?

                    • 7. Re: How to create drill-down Actions on bar charts with multiple data sources
                      Srinidhi Narayan

                      Jaskaran

                       

                      Though I have a solution for you here, there is still a thing or two that need clarification: I think you want two criteria to drill down by - the year, and the net worth category - correct?    You have several date fields in your detail.. which date field matches up to the year from the summary?

                       

                      Attached is a workbook that "attempts" to address your issue.  though i dont know if I have the relationships correct.    I used the reshaped excel file in the attached, and kept your other data source the same.  What is needed here is a "blend" of data.  And the blending has to be done using the right fields.  I believe you need a combination of the year, and the net worth category (at least that is the assumption I ran with). 

                       

                       

                      in the attached i have done the following:

                       

                      - Define A custom Blending relationship across the two data sources.  You will see how this is done if you go to Data -> Edit relationship.  This drives the relationship between the data sets.

                      - I also had to edit your calculation for the network category to match the values across the two data sets so the blend actually returned some results.

                      - I created an additional calculation for extracting the "year" date part on both data sources.  (this is where I made the assumption, and used the "added date" to blend on for your detail).  

                      - On the dashboard - created a dashboard action that uses the relationship to drill down on.  You can see that under Dashboard -> Actions.

                       

                      Again - I need to reemphasize that I am merely showing you the technique in the attached workbook - I do not know enough about your data and the relationship, so i cannot speak to the accuracy of the results, as  I made some assumptions on which date field to use.

                       

                      I hope this helps!

                      1 of 1 people found this helpful