We had a case recently where a customer wanted to use their daily maximum sales value (in any dimension) as a dynamically changing target that could also be controlled interactively to reduce that maximum to a more achievable level.
My colleague Debbie Lonsdale constructed a Dashboard for them to manage the target based on the daily maximum sales, but which they could control with a List Quickview to explore some “what-if” targets in place of a budget file. This technique could equally be used on Minimums and for other dimensions or summaries.
Just a word of caution – this data set was quite small – don’t try this on a huge data set. If you have a large data set it might be more efficient to ask your Diver developer to pre-calculate some of these columns in the Visual Integrator so that they are in the model ready for you.
Click on the images to enlarge
1. The Dashboard below shows that when the Target Control is set to 100%, the Daily Target = the Maximum Daily Sales achieved for the whole data set. The cumulative target values are visualised in the graph bottom right. The green line is the target, purple is the actual, and lilac is the average.
4. The calculations for the Measures Portlets were set up in ProDiver in the columns highlghted – as shown in the steps below. We used some of the Functions that help with getting the max and the parent values.
7. This allows you to find the maximum daily sales value from all the data. It is shown in the total field. We use the Function “DimMax” with the Dimension”Trans Date” and the Summary “Sales” to find the Maximum Sales by that Dimension for all the data. This is the nifty bit that wasn’t obvious.
These columns are now used separately in each of the Measures Portlets and the graphs on Diveport, and are controlled by the Quickview Portlet to give Dynamic results. We just format the numbers and colours to make things clearer than in ProDiver!!