topic Re: Average calculation in QlikView

Average calculation

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

Hi there,

How can i calculate below table average by Segment.

It is must be,

This is my Expressions and table

count(distinct if(AMOUNT>0 AND TYPE='ACTUAL', SPECIALTY))

devarasu07 — Fri, 15 Dec 2017 07:52:13 GMT

Hi,

Can u try like below,

=Avg(aggr(count(distinct if(AMOUNT>0 AND TYPE='ACTUAL', SPECIALTY)),SEGMENT))

or try other way simplified using set analysis method

=Avg(Aggr (count({$<AMOUNT={">0"}, TYPE={'ACTUAL'}>} distinct SPECIALTY),SEGMENT))

if still have a issue, share the mock data. thanks

also check this Hic article,

jonathandienst — Fri, 15 Dec 2017 08:06:22 GMT

Looks correct, but probably more efficient to use

=Avg(Aggr(Count({<AMOUNT = {">0"}, TYPE = {'ACTUAL'}>}distinct SPECIALTY), SEGMENT))

big_dreams — Fri, 15 Dec 2017 08:08:42 GMT

can you provide sample data???

Regards,

devarasu07 — Fri, 15 Dec 2017 08:19:02 GMT

Hi,

can your share your mock data in excel format? that would be easy for us to check and help you

devarasu07 — Fri, 15 Dec 2017 08:27:04 GMT

Hi,

just add your client dimension

=Avg(Aggr(Count({<AMOUNT = {">0"}, TYPE = {'ACTUAL'}>}distinct SPECIALTY), SEGMENT,CLIENT))

for the total just enable total check box on your straight table --> dimension limit tab section.

asknyldz — Fri, 15 Dec 2017 08:28:08 GMT

Thanks Devarasu