Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Comparing average with the field

Hi,
I have following data sample:


ID    Category    Value      Flag

  x         A                12      Chosen

  x         B                10      Cancelled

  x         C                 5      Converted

I need to compare average value (on id level) with that of value of category with 'Chosen' Flag

The output I need is this:

ID       Category         Value   Average Value

x             A                  12         9 (Average of 12,10 and 5)

I tried applying filter (Flag=Chosen), and ruled it out while calculating average. But, that does not seem to work:


avg({$<[Category]=,[Flag]=>}[Value])

This would ignore Category and Flag while calculating the average.

Tags (1)
7 Replies

Re: Comparing average with the field

Try: avg({$<[Category]=,[Flag]=>} TOTAL [Value])

- Marcus

Re: Comparing average with the field

=avg(TOTAL {<Category, Flag>}Value)

Not applicable

Re: Comparing average with the field

Hi Marcus,
Thanks for the reply but isn't working.

Not applicable

Re: Comparing average with the field

This would give the overall average (same value for each row on id level).

Re: Comparing average with the field

If you are looking for different AVG based on ID use below

=avg(TOTAL <ID> {<Category, Flag>}Value)

Not applicable

Re: Comparing average with the field

Hi Manish,
By using the above query, filter (Category=Chosen) cannot be applied, please help. Will any query be required for Id (As dimension and value) as well?

Re: Comparing average with the field

Provide sample data along with the output you want in excel.