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())
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')
=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())
=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.
★ ★ ★
8 comments:
Hey, I've never used your way but i think it's a good idea.
I usually have to show (as you explained) just a group of the dimentions, for example: for rankings!
In order to get that, what i do it's to delimitate in the Presentation Tab how many values i want to see.
I was wondering the difference between your way and mine, and i noticed that even if i show only 15 , my chart will have the scroll after them. So, i'd see all of the results but in 15 sets.
Even though, if i recall well, i think it's optional to enable the scroll.
Any other difference?
Hi Leonor,
Thanks for your comment. I think the biggest difference between the two methods is that the calculated dimension can rank things based on any measure whether it appears in the chart or not. So, for example, a calculated dimension can select the ten top customers based on sales volume even though the chart just shows credit history and not sales volume.
We have noticed a pretty substantial performance hit when using calculated dimensions. Have you run into this at all?
Hi Brian,
I haven't noticed performance issues with the kind of calculated dimensions described here which are really just conditional tests. I haven't done much testing of these on our gigantic documents, though. I think it might be more of a problem with a calculated dimension that makes QlikView sort and aggregate the data again; and it would be worse if you are using multiple calculated dimensions.
Hello
A useful idea. But I tend to avoid calculated dimensions where I can because they can cause problems - known ones such as Sort order not working correctly, and also unpredicatble ones !
Anyone else experience this?
Hi
I need to calculate a dimension based on a selection from the end user:
-running
-jogging
-hikking
-skiing
The end user can define as "snow activities" any of those listed, and the remaining should be listed individually; imagine the end user selected (hikking ans skiing) as snow activities, the resulting table should show as column:
snow activities (sum of values for hikking and skiing) but also at the same level running and jogging... any idea about how to proceed?
cheers
cmpli@yahoo.com
I've spent four hours trying to figure this out and then found this... Thanks for an excellent knowledge share... Now if I could just get it to SORT on the calculated dimension, I'd be all set. Thanks a BUNCH!
Hi,
I have to exclude some division and sub-division for 2013 only. I am using cyclic Group to drill division to sub-deivision now. Can I you your idea for this exclude purpose. Or do you have any idea to do so.
Thanks,
Dust
Post a Comment