Delta in data

Mees · February 22, 2010 4:31PM

Hi,

I want to visualise the difference between two bars in my bar view.
Situation is as follows:
- dataset consists of clients with sales per product per period
- bar view is split on sector and horizontally paned by year
- Measure is Sales
- Sector A has less sales in 2010 compared to 2009 as the bar is smaller in the 2010 pane

Assume that clients were lost and this has caused the drop in sales. How can we configure Omniscope to identify which clients make the difference?

Thanks

tjbate · February 23, 2010 6:13PM

Can you either post a 'sanitised' (scrambled and otherwise disguised) version of your data set, or send it to us directly?

March 1, 2010 9:30PM

Mark

You could create a formula, using the SUBSET commands, to calculate the difference in sales between 2010 and 2009 for each client which exists in 2009 - those with a 100% decline have been lost. Something along the lines of:

* Create New formula field "Next Year Sales"

if([Year]="2009",
subset_sum([Sales],subset3([Client],[Sector],[Year],[Client],[Sector],"2010"))-subset_sum([Sales],subset3([Client],[Sector],[Year],[Client],[Sector],"2009"))/subset_sum([Sales],subset3([Client],[Sector],[Year],[Client],[Sector],"2009")),
0
)

This could be improved (for readability) using the new DECLARE function in version 2.6, but the above should work... it assumes that you want to look at total year-on-year sales for each Client within a Sector, and presents the result as a % figure of 2010 sales against 2009 sales... a value of -100% is a Client lost in a specific Sector.

Welcome!

Categories

Ideas Parade