topic Re: Help required with Expression in QlikView

Help required with Expression

awhitfield — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help required with Expression

sunny_talwar — Wed, 05 Jul 2017 11:24:16 GMT

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

Re: Help required with Expression

arvind_patil — Wed, 05 Jul 2017 11:25:12 GMT

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

Re: Help required with Expression

sunny_talwar — Wed, 05 Jul 2017 11:25:33 GMT

Just to throw out an idea, may be this?

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

Re: Help required with Expression

awhitfield — Wed, 05 Jul 2017 11:25:41 GMT

Hi Sunny,

sorry, they are the same thing! Whoops!

best regards

Andy

Re: Help required with Expression

sunny_talwar — Wed, 05 Jul 2017 11:26:54 GMT

And is Value a field or an expression?

Re: Help required with Expression

awhitfield — Wed, 05 Jul 2017 11:34:58 GMT

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

Re: Help required with Expression

sunny_talwar — Wed, 05 Jul 2017 11:37:19 GMT

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....

Re: Help required with Expression

awhitfield — Wed, 05 Jul 2017 12:01:08 GMT

sorry still not getting it, example qvw attached

Re: Help required with Expression

sunny_talwar — Wed, 05 Jul 2017 12:06:14 GMT

May be this

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

Re: Help required with Expression

awhitfield — Wed, 05 Jul 2017 12:10:53 GMT

Nice one Sunny, not surprised it was causing a brain explosion!

Many Thanks

Best Regards

Andy

Re: Help required with Expression

awhitfield — Thu, 06 Jul 2017 08:21:12 GMT

Hi Sunny,

thanks again for the solution, but cpildyou break down and explain how the expression actually works?

Thanks in advance

best Regards

Andy

Re: Help required with Expression

sunny_talwar — Thu, 06 Jul 2017 09:00:54 GMT

So I basically I am summing the value for each commissioner_code and programme_Category and ignoring the selection in commissioner_code

Sum({<commissioner_code>}Value)

Next we needed the Avg across all the commissioner_code, so used TOTAL Qualifier with the field name yet again ignoring selection in commissioner_code.

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

Now, since I am ignoring selection in commissioner_code, any selection in commissioner_code will keep showing all the rows. In order to only show the single selected row, but still show the overall average, I multiplied it with Avg(1). Avg(1) will equal 1 for selected commissioner_code and will equal 0 for all other making them all 0.

Does this help?

Re: Help required with Expression

awhitfield — Thu, 06 Jul 2017 09:31:46 GMT

Superb!