Search code examples
visualizationdata-visualizationspotfire

Dynamic Spotfire KPI Formula Based on Selected Master KPI

I have a master KPI chart that has a detail KPI chart listening to it. The master KPI chart can have two measures (for simplicity): Eng Displ and Cylinders. The detail KPI chart then responds when the Eng Displ KPI is selected to show Cylinders broken down by business area:

enter image description here

I now want the detail KPI chart to break down Cylinders if it is selected without creating another details visualization (I want it all in the same visualization).

tl;dr I want a details visualization to change formulas depending on which part of a master visualization is selected.

Solution

  • There are a few steps to doing this.

    1. Set up a document property. We will assign a value to this in the next step through the use of a Python script later.
    2. Set up a python script for each master KPI (in my case, two). This script will look similar to this (this will also live in docuent properties - Scripts): Document.Properties['KPIMeasure'] = '(Avg([Eng Displ]). Change the script formula depending on the KPI you are creating it for, but have it assign to the same Document Property you created earlier.
    3. Create an action on each master KPI that calls the appropriate script (the script with the correct formula). When the master KPI is clicked, the script will fire, and set the formula for the detail KPI accordingly (in the step below).
    4. In the detail KPI, create a custom expression as ${KPIMeasure}. This will be set when a master KPI is selected to whatever formula that master KPI has (whatever is in the Python script).

    Of course, you can edit the python script to also change the name of the detail KPI dynamically as well. The one downside of this is column names, if changed in the table, will not update automatically in the Python script because it is seen as just a string.