Help required with Expression

[awhitfield]

Can any one help with this one please?

In the source data there are lots of rows for Adverse effects, but when you group up by ccg there are 12 sums to be averaged:

CCG	programme_category	Value	Avg
00T	Adverse effects and posioning	30607	61579
00V	Adverse effects and posioning	80011	61579
00W	Adverse effects and posioning	2274	61579
00Y	Adverse effects and posioning	48864	61579
01D	Adverse effects and posioning	91356	61579
01G	Adverse effects and posioning	121482	61579
01M	Adverse effects and posioning	114102	61579
01N	Adverse effects and posioning	10377	61579
01W	Adverse effects and posioning	75541	61579
01Y	Adverse effects and posioning	27386	61579
02A	Adverse effects and posioning	3720	61579
02H	Adverse effects and posioning	133236	61579

The average for the 12 would be 61579, the 61579 is what we’d want displayed in the column.  The idea being, we could compare the sum of the cost for the CCG in the chart against the average sum of costs across the selections.

But (where it gets messier), if we choose a commissioner_desc from the filter, we’d expect the average of sums to still be of the 12 ccgs, not just the ‘one’ selected and displayed in the chart.

Any Ideas please

best regards

Andy

[sunny_talwar]

Hey Andrew, what is commissioner_desc here? Is it somehow related to CCG? Also, is Value just Value or is it Sum(Measure)?

[arvind_patil]

Hi Andrew,

Maybe you have to nullify commissioner_desc in set analysis.

Like:

Avg( {<commissioner_desc =>}Value)

Then It will not affect your data.

Thanks,

Arvind Patil

[sunny_talwar]

Just to throw out an idea, may be this?

Avg(TOTAL <programme_category> Aggr(Sum({<commissioner_desc>}Measure), CCG, programme_category))

[awhitfield]

Hi Sunny,

sorry, they are the same thing! Whoops!

best regards

Andy

[sunny_talwar]

And is Value a field or an expression?

[awhitfield]

Hi Sunny,

it the REAL dashboard it's a pretty messy expression, but in this example it's just a value. To make it clearer, the data is as follows:

commissioner_desc	programme_category	Value
00T	Adverse effects and posioning	30607
00V	Adverse effects and posioning	80011
00W	Adverse effects and posioning	2274
00Y	Adverse effects and posioning	48864
01D	Adverse effects and posioning	91356
01G	Adverse effects and posioning	121482
01M	Adverse effects and posioning	114102
01N	Adverse effects and posioning	10377
01W	Adverse effects and posioning	75541
01Y	Adverse effects and posioning	27386
02A	Adverse effects and posioning	3720
02H	Adverse effects and posioning	133236

[sunny_talwar]

I guess replace your expression with this

Avg(TOTAL <programme_category> Aggr(Sum({<commissioner_desc>}Measure), CCG, programme_category))

Replace the red part with your original expression....

[awhitfield]

sorry still not getting it, example qvw attached

[sunny_talwar · Accepted solution]

May be this

Avg({<commissioner_code>} TOTAL <programme_category> Aggr(Sum({<commissioner_code>}Value), commissioner_code, programme_category)) * Avg(1)