10 Replies Latest reply on Nov 16, 2018 10:28 AM by Joe Oppelt

    Create dynamic legends

    Julie Lawson

      I've wasted several days and used up all of my "Can you help me" cards at work trying to solve this problem. I keep thinking I'm on the brink of a fix, but it's time to admit none of us know the solution. I'm hoping someone out there knows a trick to make it work and have attached a simplified workbook as an example.

       

      Problem: When dynamically shifting between two views of a graph on a dashboard, only one legend is visible (the one in front). If you switch views, the legend that the user can see becomes null because it's no longer in use. It hides the legend that is now in use. I need the legend that corresponds to the displayed graph to display dynamically. Maybe another way to say this is that I need to suppress legends that are not currently in use. I do not want to keep the legends side by side. That would mean showing 8 legends for one tiny graph in my real world scenario.

       

      Any help would be greatly appreciated!

        • 1. Re: Create dynamic legends
          Joe Oppelt

          You need to watch the videos linked here:

           

          New series of videos on swapping and popping on a dashboard

           

          I do precisely that in the videos.  If you want the legends to be interactive, you need to use the method done in part-3.  If you just need them displayed and not interactive, the step 2 is a bit easier and will do the trick.

          • 2. Re: Create dynamic legends
            Joe Oppelt

            I implemented step 2 in your dashboard.

            • 3. Re: Create dynamic legends
              Hari Ankem

              For your requirement, you do not need sheet swapping. You can achieve the same functionality using a single worksheet itself. I have placed the new sheet on the same dashboard above your current chart so that you can compare the outputs.

              1.png

              1.png

               

              I have created a calculated field as shown below and using it to display the required value.

              1.png

               

              Hope this helps. Updated workbook attached.

              • 4. Re: Create dynamic legends
                Julie Lawson

                Thank you, Joe, but I've already watched this and several other videos. It doesn't fix my issue. I even pressed pause in between each step to ensure I didn't miss anything. It could be because I'm working with 4 worksheets, not 2.

                • 5. Re: Create dynamic legends
                  Joe Oppelt

                  Does the example I uploaded yesterday do what you are looking for?  (Assuming 2 sheets.)  That can be expanded to 4 sheets.  (Or 100!)

                  • 6. Re: Create dynamic legends
                    Julie Lawson

                    Thank you, Hari. This works for the example I provided. Unfortunately, I gave too simple of an example. I'm trying to display individual and aggregated values from multiple data sources for daily %, weekly %, daily total, and weekly total. There are 8 legends involved.

                     

                    I tried to modify the calc you provided, but it throws the error message that I cannot mix aggregate and non-aggregate arguments with the case function. Here is what I tried:

                     

                    DATE (CASE [buttons]

                    WHEN "Daily %" THEN DATETRUNC("day", [Master Dates])

                    + AVG([aggregated daily % value])

                    + SUM([non-aggregated daily % value])

                    WHEN  "Daily Total" THEN DATETRUNC("day", [Master Dates])

                    + AVG([aggregated daily total value])

                    + SUM([non-aggregated daily total value])

                    WHEN  "Weekly %" THEN DATETRUNC("week", [Master Dates])

                    + AVG([aggregated weekly % value])

                    + SUM([non-aggregated weekly % value])

                    ELSE DATETRUNC("week", [Master Dates])

                    + AVG([aggregated weekly total value])

                    + SUM([non-aggregated weekly total value])

                    END)

                     

                    I also tried this, which yields an error that all values must be aggregate or constant because I’m using multiple data sources:

                     

                    DATE (CASE [buttons]

                    WHEN "Daily %" THEN DATETRUNC("day", [Master Dates])

                    + [aggregated daily % value]

                    + [non-aggregated daily % value]

                    WHEN  "Daily Total" THEN DATETRUNC("day", [Master Dates])

                    + [aggregated daily total value]

                    + [non-aggregated daily total value]

                    WHEN  "Weekly %" THEN DATETRUNC("week", [Master Dates])

                    + [aggregated weekly % value]

                    + [non-aggregated weekly % value]

                    ELSE DATETRUNC("week", [Master Dates])

                    + [aggregated weekly total value]

                    + [non-aggregated weekly total value]

                    END)

                    • 7. Re: Create dynamic legends
                      Julie Lawson

                      Just to expand a little, here is my full problem:

                       

                      I'm trying to display individual and aggregated values from multiple data sources for daily %, weekly %, daily total, and weekly total. There are 8 legends involved.

                      • 8. Re: Create dynamic legends
                        Joe Oppelt

                        Again:  Does the example I uploaded yesterday do what you are looking for?

                        • 9. Re: Create dynamic legends
                          Julie Lawson

                          Hi Joe –

                           

                          Your solution is elegant and works. The problem is that I have the date variables for 4 different values. I need to show:

                           

                           

                          §  View 1

                           

                          o   Total count by day (measure 1, date 1)

                           

                          o   Total aggregated count by day (measure 2, date 1)

                           

                          §  View 2

                           

                          o   Percentage by day (measure 3, date 1)

                           

                          o   Average percentage by day (measure 4, date 1)

                           

                          §  View 3

                           

                          o   Total count by week (measure 1, date 2)

                           

                          o   Total aggregated count by week (measure 2, date 2)

                           

                          §  View 4

                           

                          o   Percentage by week (measure 3, date 2)

                           

                          o   Average percentage by week (measure 4, date 2)

                           

                          I’ve tried a couple of variations on the calc, but I cannot get it to work.

                           

                          Thanks,

                           

                          Julie

                          • 10. Re: Create dynamic legends
                            Joe Oppelt

                            I'm not sure what you want me to do with that.


                            If I had your workbook I could show you what you need to do.

                             

                            If you don't want to post your workbook here, email me as shown in my profile.