Calculated Dimensions to Show Things Satisfying a Condition

We often use a Cycle Group for the dimension on our charts. That allows the report user to switch between different dimensions easily. I’ve found it useful to add a few extra calculated dimensions that only show a particular group of things – a group that is useful to the report user or a group that requires attention.
For example, I often include a calculated dimension that only shows the top 15 items by shipment volume (it could just as easily be the top fifteen salespersons, or top 15 most requested service kits, or whatever). The expression for a calculated dimension like that is:
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM,null())

You will need to check off the Suppress When Value Is Null button on the Dimension tab of the chart properties. If it is important to you for the totals in the chart to remain correct for the entire population then change the code slightly so that the dimension shows the top 15 items and one choice for all other items something like this:
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM, 'All Others')

With a chart dimension like that the chart changes as the report user changes the selection. When the selection is Country=FRANCE, for example, the chart shows the top 15 items sold in France, and if the selection is Product=CLOTHING then the chart changes to show the 15 clothing items based on shipment volume. If the report user wants to make a selection for the 15 items then that is as simple as dragging the cursor over the 15 items listed in the chart.

In my job we are always looking for things that adversely affect the quality of sales forecasts. One example of something like that are items on the forecast reports that have no shipments. Unless the item is a very new product it should have shipments. When there’s no shipments for a forecasted item that often is a clue that there is a data problem. A calculated dimension for items with no shipments might look like this:
=If(sum(SHIPMENTS)<=0,ITEM,null())

A more specific calculated dimension expression that looks for items with a forecast but no shipments might look like this:

=If(sum(SHIPMENTS)<=0 and sum(FORECAST)>0,ITEM,null())

Similar calculated dimensions might look for items with no sales orders or no bill-of-materials or missing pricing data, etc.

Consider using a calculated dimension when your report users often have to review data for a particular group of things. The calculated dimension may be able to easily isolate a group of things that satisfy a condition even when doing the same task as a selection is complex.

In my next blog post I'll describe how to show and select a group of things satisfying a condition with a listbox or multibox.

★ ★ ★