topic Re: Average calculation over dimension in QlikView

Average calculation over dimension

qlikknow — Sat, 16 Nov 2024 03:30:16 GMT

Hi,

I am trying to calculate overall average over a dimension.As shown in below table how to calculate avg for Count column and display same overall average for all dates.Tried Aggr and avg in expression but no luck.

Thanks in advance

Re: Average calculation over dimension

anushree1 — Thu, 23 May 2019 07:01:57 GMT

please provide a sample of input and desired output

Re: Average calculation over dimension

jaibau1993 — Thu, 23 May 2019 09:13:50 GMT

Hi!

I am not sure if I understood your issue but I think that the following expresion may work:

Avg(Total Aggr(Count(Value), Name, Date))

Where I supposed that your count expression is "Count(Value)". Please, use your correct count expression above. I get the following output:

Hope it helps! Regards,

Jaime.

Re: Average calculation over dimension

qlikknow — Tue, 28 May 2019 20:12:23 GMT

Thanks @jaibau1993 it worked added dimension after Total.I want to make small change to avg.In numerator I want to consider all records (4 records as shown) for Avg but divided by 3 instead of 4.So for ABC I want to show 10/3=3.33 instead of 10/4=2.5.thanks in advance

Avg(Total<Name> Aggr(Count(Value), Name, Date))

Re: Average calculation over dimension

jaibau1993 — Wed, 29 May 2019 06:19:46 GMT

Hi!

The following expression does what you need:

Sum(Total Aggr(Count(Value), Name, Date)) / (Count(Total Aggr(Count(Value), Name, Date)) - 1)

Regards,

Jaime.

Re: Average calculation over dimension

qlikknow — Wed, 29 May 2019 12:16:42 GMT

Thanks.It worked