topic Re: Average Total for all rows in QlikView

Average Total for all rows

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

Hi Experts,

For your reference i am attaching snapshot.

Here i want show them the total average(95.3) in all rows.which is showing in snapshot with black mark.

My expression is

Count(DISTINCT UNIX_ID_GOV)/

Count(DISTINCT total UNIX_ID_RST)

Expected Output:-

theresaa‌

Regards

Mahesh

Re: Average Total for all rows

sunny_talwar — Wed, 18 Apr 2018 14:00:53 GMT

Try this

Count(TOTAL DISTINCT UNIX_ID_GOV)/

Count(TOTAL DISTINCT total UNIX_ID_RST)

or this

Avg(TOTAL Aggr(

Count(DISTINCT UNIX_ID_GOV)/

Count(DISTINCT total UNIX_ID_RST)

, MonthName))

Re: Average Total for all rows

vishsaggi — Wed, 18 Apr 2018 14:01:10 GMT

Not tried you can try like

= Avg(TOTAL Aggr(

Count(DISTINCT UNIX_ID_GOV)

Count(DISTINCT total UNIX_ID_RST)

, MonthName))

Re: Average Total for all rows

vishsaggi — Wed, 18 Apr 2018 14:04:31 GMT

Huh!!! I am late. stalwar1‌ you cannot go against nature laws. Your responses are travelling 299 792 458 m / s...Not good. (Speed of light)

Re: Average Total for all rows

jubarrosor — Wed, 18 Apr 2018 14:05:02 GMT

Hi:

You can use the function total:

Count(total<MonthName> DISTINCT UNIX_ID_GOV) / Count(total<MonthName>DISTINCT total UNIX_ID_RST)

Best Regards,

Juan P. Barroso

Re: Average Total for all rows

qlikview979 — Wed, 18 Apr 2018 14:09:24 GMT

Hi,

Thanks for ur reply,

I tried same expressions what u shared ,first expression is showing instead of 95.3 it is showing 111 for all rows

and second one is showing 88.8 for all the rows.

Regards

Mahesh

Re: Average Total for all rows

vishsaggi — Wed, 18 Apr 2018 14:12:06 GMT

Did you remove Avg from your Total function drop down as shown inyour snapshot. You this as your custom expression to calculate avg.

Re: Average Total for all rows

sunny_talwar — Wed, 18 Apr 2018 14:13:43 GMT

Do you have a null dimension which you might be suppressing?

Re: Average Total for all rows

qlikview979 — Wed, 18 Apr 2018 14:15:30 GMT

not working

Re: Average Total for all rows

qlikview979 — Wed, 18 Apr 2018 14:18:46 GMT

yes

Re: Average Total for all rows

qlikview979 — Wed, 18 Apr 2018 14:19:52 GMT

Hi Bro,

we don't have null values in dimension level .

Re: Average Total for all rows

jubarrosor — Wed, 18 Apr 2018 14:20:43 GMT

Ouch!

Sorry:

Count(total<MonthName> DISTINCT UNIX_ID_GOV) / Count(total<MonthName>DISTINCT total UNIX_ID_RST)

Re: Average Total for all rows

qlikview979 — Wed, 18 Apr 2018 14:25:27 GMT

sorry bro,

its not working

Re: Average Total for all rows

sunny_talwar — Wed, 18 Apr 2018 14:28:39 GMT

Have you unchecked 'Include Zero Value' from your MonthName dimension? Can you check it, if it is unchecked?

Re: Average Total for all rows

qlikview979 — Wed, 18 Apr 2018 14:38:31 GMT

Yes, please find the attached Sample data .

Re: Average Total for all rows

jubarrosor — Wed, 18 Apr 2018 14:39:58 GMT

Sorry friend:

avg(total aggr(sum( DISTINCT UNIX_ID_GOV)/ sum(total DISTINCT UNIX_ID_RST), Mes))

Best Regards,
Juan P. Barroso

Re: Average Total for all rows

sunny_talwar — Wed, 18 Apr 2018 14:40:22 GMT

This is a pre-aggregated data (looks like an export from your chart) so I can't really use it for anything .

Do you have a calculated dimension?

Re: Average Total for all rows

qlikview979 — Wed, 18 Apr 2018 14:58:42 GMT

Hi Bro Thanks for ur reply,

Instead of SUM i have used COUNT in expression level, but here instead of 95.3 it is showing 88.8

I have attached sample if its possible can you check it.

Regards

Mahesh

Re: Average Total for all rows

qlikview979 — Wed, 18 Apr 2018 15:02:17 GMT

Hi Bro,

i don't have any calculated dimension.

Re: Average Total for all rows

sunny_talwar — Wed, 18 Apr 2018 15:03:40 GMT

No idea, would you be able to share a qvf sample?