One of the questions that came up in the forums the other day was how to display multiple KPI's for a set of years using the accordion menu. It was further complicated by the fact that some of the KPI's were absolute figures such as sales and the other KPI's were percentages such as growth rate.
Seemed like a good problem to solve and I had some free time today, so thought if tackling this. The dashboard looks like follows
What is cool about this is Y axis reflects the nature of the KPI. For sales and Inventory it will show in $'s and for others the axis shows % values. There is a little trick that I used for and I will share it in a later part of this document.
Let us start with the basics. In order to build an accordion style dashboard we need the data in a particular format. The screenshot of the excel file I started out with is below.
For each year I am capturing the trend for the KPI's. I could have arranged the excel the other way around as well and it would have still worked. Once I have the data I just need to drag and drop the accordion menu and the graph from the pallete and I can be off and running.
For the accordion menu the settings are simple enough. See below
| I have set the insertion type as column, which essentially means that when the user selects one of the KPI's from the menu, the entire column for that year is copied and inserted into the destination (cells C88:C100). And I also bind each of the years with the category as the source data. Essentially when the user picks the category, XCelcius knows which data block to go to. Then when the user clicks on the specific KPI, the data for all the months for the KPI is copied to the target. |
||
The chart reads from the target and displays the results. If all the KPI's had the same scale then that's all I would need to do.
In my case there is an additional level of complexity. I have KPI's with very different scales and the chart does not display those correctly (not sure if that's the way it is supposed to behave). To solve that problem I did a little trick, Instead of
| Instead of 1 chart, I have 2 charts… and I set the dynamic visibility of the charts to be driven based on the KPI selected. |
||
| I have 2 sections in excel, one to display % values and another to display absolute values and I drive the charts from different sections. Since the accordion can only fill one section, I have used formulas to populate the data in the 2nd region. Now I define a flag using an excel formula =IF((C88 = "Sales"), 1,IF((C88 = "Inventory"),1,0)) that controls which chart will be displayed. |
||
Using this approach I am able to hide one chart, and show the correct chart based on the selection. Not sure if there is a better way, but this works J
You can get the XLF file at http://www.box.net/shared/gg9f6lnh90