8 Replies Latest reply on Apr 26, 2018 7:53 AM by Joe Oppelt

    Adding Filterable KPIs to dashboard

    Walker Peterson

      I am having trouble adding single number KPIs to this dashboard. I want single numbers for % change over the time period filtered for on the dashboard placed in above the graph. I attempted this with Table calculations on another sheet, but could not get it to show a single number calculation without affecting the calculation.

        • 1. Re: Adding Filterable KPIs to dashboard
          Joe Oppelt

          Your workbook is trying to long into MySQL.


          Extract your data source and use the extract (or just dump a handful of rows into an excel sheet and change your data source to use that) so that the data is pulled into the TWBX.


          Check out this link for more discussion on how to get your data into the TWBX:


          Packaged Workbooks

          • 3. Re: Adding Filterable KPIs to dashboard
            Joe Oppelt

            OK, I can open it now.

            So explain what you need to do here.  I see Sheet 2 gives you a chart for a range of dates.

            Sheet 3 looks like it has a series of table calcs that show %change from previous.  The question I have is:  Previous to what?


            And you have a DMA_NAME filter on sheet 2.  Do you want that to impact Sheet 3 as well?

            • 4. Re: Adding Filterable KPIs to dashboard
              Walker Peterson

              I want Sheet 3 to display the percent difference from the beginning of the filtered time range to the end of it and it to also be filterable by DMA. Getting Sheet 3 to calculate along these filters is what I am having an issue with.

              • 5. Re: Adding Filterable KPIs to dashboard
                Joe Oppelt

                See attached.


                If you do "Apply to" on the filters, they'll work on the sheets you specify.



                So I did that to your two filters.


                Also, if you put the filters "in context", then FIXED LODs obey them.


                With that, I could create a [First Date] and [Last Date] calc, and then create calcs to grab the value of active devices on the first day and the last day.  (And by putting them into context, it eliminated the need for your [Active Devices] calc.  I could just go with your data source field.)


                You can see some of those values in the tooltips on sheet 2.


                On sheet 3 I changed it so we have the exact panel date on the sheet.  We need that to get individual date values for the %diff calc.  I took off some of your calcs, and added mine.  You can't just do a quick calc for %diff of prior.  That gives you %diff for each day compared to the prior day.  You need to grab the specific values and make your own calc.  You can see the syntax in my calc.  The value is the same all the way down the chart.  Go to version 2 of sheet 3.


                Here I moved the date pill to details.  That gives us 55 copies of the value in the mark (or as many days as were selected by the user.)  I made a calc called [index] and I put that on the filters shelf.  I set the table calc setting to run along date.  And then I set the filter to get only value = 1,  Now we just get the first copy of the value.

                1 of 1 people found this helpful
                • 6. Re: Adding Filterable KPIs to dashboard
                  Walker Peterson

                  That worked for a minute but now I am having the same issue when I recreate it across multiple measures. I want the attached graph with updating percent changes for all variables. I have created all of the calculated fields identically but it only updates the graph and not the calculations.

                  • 7. Re: Adding Filterable KPIs to dashboard
                    Joe Oppelt

                    One big key you were missing was to put the [panel_date] filter in context.  I have that now in the attached.


                    Sheet 1 doesn't care about the context, but the FIXED LOD calcs used for Sheet 2 DO need that filter in context.  (When a filter in in context, a FIXED LOD will evaluate AFTER the filter is exercised.  When it is not in context, the FIXED LOD operates before the filter is exercised.)  I think I discussed that step in my previous reply.


                    On a separate note, I don't understand what's happening with [Active Households] vs [sum(active_households)].  On sheet 1 I added [sum(active_households)] to tooltips.  When I run the cursor along the orange line the values of [Active Households] and [sum(active_households)] are identical.  But when I swap  [Active Households] and [sum(active_households)] back and forth in the [First Date Active Households] calc, I get different results.  And using [sum(active_households)] gives m the correct results.  What are you addressing when you do the INCLUDE [dma_names] in the [Active Household] calc?


                    That question aside, putting the date filter in context lets Sheet 2 get the right values.

                    • 8. Re: Adding Filterable KPIs to dashboard
                      Joe Oppelt

                      Huh.  I just went back and re0read my previous reply.  I only mentioned "in context" in passing.  I should have emphasized it there.