topic Consolidating data to new chart type in QlikView

Consolidating data to new chart type

Wed, 14 Nov 2012 10:13:34 GMT

Hello,

I'm not sure if the below is possible but thought I'd ask for some suggestions. I have a number of KPIs that are used across a number of different sites and all of which have their own targets, on which a RAG status is calculated. What I require is, that rather than the KPIs being displayed in a table format with their RAG colour beside them, all of the RAG statuses are consolidated into a Pie Chart with it simply showing the breakdown of the RAGs, e.g. 50% of the KPIs are green and this is shown with a pie chart being broken up to display half of it green.

The current chart has variables in it so that end users can select particularly weeks to see the spread across differents sites. We have 4 sites and I would want to see 4 different pie charts showing their respective RAG statuses.

Let me know if you require any further information.

Thanks,

Ralph

KPI	Data	RAG
Sales	30	Red
Profit	35	Green
Attendance	2	Green
CoS	34	Amber
Accidents	4	Red
Share	32	Green

Re: Consolidating data to new chart type

Gysbert_Wassenaar — Wed, 14 Nov 2012 10:42:01 GMT

You can use the trellis option to display a pie chart for every site. So site is one dimension, RAG is the second dimension. And count(KPI) is the expression.

You can use this expression for the background color of the expression to set the color of the pie segments: pick(match(RAG,'Red','Green','Amber'),LightRed(),LightGreen(),RGB(255,155,0))

Re: Consolidating data to new chart type

Wed, 14 Nov 2012 10:45:53 GMT

RAG is calculated based on the Data. i.e. for the above example for Sales, the target is 25 and that is why it is showing up as a RAG status of Green. Would that work as a calculated dimensions?

Thanks,

Ralph

Re: Consolidating data to new chart type

Gysbert_Wassenaar — Wed, 14 Nov 2012 10:49:00 GMT

The expression you use to calculate RAG should work as a calculated dimension.

Re: Consolidating data to new chart type

Wed, 14 Nov 2012 11:13:55 GMT

Hello,

For information, below you can see how I currently attach a RAG status against each KPI. I'm not sure this can be added as a second dimension as each KPI has a different method to calculate the RAG status

if(sum({<Site={"Dublin"},Week={$(=vCurrentWeek)}>}[Sales]) >
avg({<Site={"Dublin"},KPI={"Sales"}>}Base),'red',

if(sum({<Site={"Dublin"},Week={$(=vCurrentWeek)}>}Sales]) <

avg({<Site={"Dublin"},KPI={"Sales"}>}Target), 'green', 'orange'))
"