Here is the promised follow up on the Dashboard graph. In the previous article, I posted the code to create a panel of bullet KPIs displaying three different metrics. For each KPI, I used 5 columns of data which resulted in a wide and inconvenient structure.
A more convenient data structure is shown below. In this case, the data for each KPI is classified by the name of the metric. Any one metric can have fewer or more observations. For a large panel of KPIs, this data structure is more manageable and extensible.
This data structure works really well for a LAYOUT DATALATTICE where we can just assign the ROWVAR=Metric. The layout would automatically generate the three cells for us, and use the appropriate subset of the data for each cell. The restriction is that contents of each cell will have the same structure.
That is not the case here, as we can see the second row has a reverse axis. Also, we are using custom labels on the left of each KPI. So, to make this custom layout we need to use a LAYOUT LATTICE. Every plot statement in a LAYOUT LATTICE will "see" all the observations of a column. So, we need a way to subset the data used for each cell to only the observations with the same value of the "Metric" variable. The way to do this is to use the ifc() and ifn() functions. The usage is as follows:
x=eval (ifc (metric eq 'Revenue', metric, ' ')); y=eval (ifn (metric eq 'Revenue', value, .));
In the usage above, the first parameter expression is evaluated, and if true, the second parameter is returned. If false, the third parameter is returned. So, now the code needed to draw the BarCharts and ScatterPlots in each cell is as follows:
barchart x=eval(ifc(metric EQ 'Revenue', metric, ' ')) y=level / orient=horizontal; scatterplot x=eval(ifn(metric EQ 'Revenue', target, .)) y=eval(ifc(metric EQ 'Revenue', metric , ' '));
Note: Since ORIENT=Horizontal, the X & Y roles for BarChart are flipped. X is used for vertical axis, and Y for horizontal. We should really think of X as Category and Y as Response.
Using these eval functions for the X and Y roles of the plot statements, each cell will still see all the observations. However, the observations not matching the value "Revenue" will be set to missing.
One point to remember is that for a ScatterPlot, the data range for each axis is computed independently based on all the data seen for that axis. So, we have to use the IFN() function to also set the unnecessary values for the linear axis to missing.
I have also added the use of a dynamic to control the color of the needle so the same template will work both for LISTING and JOURNAL style. I am sure there is plenty of scope to further automate this program with macro code. Here are the graphs.