Business Problem: The traffic lighting (red/green/yellow) range is different for one group/category than the others but the indicator needs to include all group values.
Recommendation: It's all in the data.
Here is an example. For the Candy_Sales_Summary data (located in the EG Sample Data folder) there are three groups of customers. The distribution of revenue within the retailer group is significantly higher than the other two groups, therefore the range is different. The table below includes the executive's defined red/yellow/green ranges for each group.
|Type||Revenue (Million)||Range (red, yellow, green)||Result|
|Grocery||$9.7||<10, 10<15, 15+||Red|
|Retailer||$46.82||<40, 40<60, 60+||Yellow|
|Wholesale||$18.59||<10, 10<15, 15+||Green|
Step 1. Update the data source to include a calculated range result based on the different groups.
You can do this any number of ways. For this example, I just used a quick sql statement and data step to get the 1, 2, 3 result based on the above criteria.
proc sql; create table vals as select type, sum(sale_amount) as rev format=dollar12. from egsample.candy_sales_summary group by type; quit; data bid.typeind; set vals; if type = 'Retailer' then do; if rev/1000000 < 40 then ind=1; else if 40 <= rev/1000000 < 60 then ind=2; else ind=3; end; else do; if rev/1000000 < 10 then ind=1; else if 10 <= rev/1000000 < 15 then ind=2; else ind=3; end; run;
The resulting data table has three columns, the type, revenue value and the indicator number.
|type||rev (in mil)||ind|
Step 2. Create a BI Dashboard Range to convert the calculated range into color categories.
In BI Dashboard, only one range can be applied to the indicator display therefore we need to create a simple range of 1, 2, 3 and use ETL code to derive the range on the data table.
Step 3. Generate an indicator
Included is a sample indicator that uses the updated data source (from step 1.) and the range (created in step 2.). Note that I modified the data definition to use the variable REV as the label for the data in IND. Otherwise the numbers displayed would be 1, 2, 3.