topic Set analysis expression to calculate the average in QlikView

Set analysis expression to calculate the average

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

Hello,

I was wondering if I could get some help please.

I am attempting to use an expression to calculate the average total sales for the month in qlikview to put in my summary table.

The raw data displays multiple sales for each day. So I would need to first sum the sales for each business day and then calculate the average for total sales for each day of that month. I have attached an example of the raw data and the desired result which can be achieved in excel. I would like to identify a simple expression to perform this calculation so that I can add it to my pivot summary table in qlikview.

Thanks,

Esther

Re: Set analysis expression to calculate the average

Anil_Babu_Samineni — Fri, 17 Nov 2017 04:29:02 GMT

I am attempting to use an expression to calculate the average total sales for the month in qlikview to put in my summary table.

Avg({<Month = {$(=Max(Month))}>} TOTAL [Sum of Deposits])

Avg(TOTAL [Sum of Deposits])

Re: Set analysis expression to calculate the average

devarasu07 — Fri, 17 Nov 2017 04:47:17 GMT

Hi,

you can try like below

sum(Deposits) / count(total distinct Month)

Re: Set analysis expression to calculate the average

Anonymous — Sun, 19 Nov 2017 22:49:21 GMT

Hello,

I attempted your above formula but it returned no results.

Re: Set analysis expression to calculate the average

sunny_talwar — Sun, 19 Nov 2017 22:54:00 GMT

Do you have a month field in your dashboard? May be it is called Month or MONTH?

Re: Set analysis expression to calculate the average

Anonymous — Sun, 19 Nov 2017 23:07:58 GMT

Yes I do.

I want to get the result in bold (see screenshot below)in my pivot table but have been unable to get

my expression to work.

Re: Set analysis expression to calculate the average

Anonymous — Sun, 19 Nov 2017 23:08:49 GMT

The result in bold is the average total for the deposits.

Re: Set analysis expression to calculate the average

sunny_talwar — Mon, 20 Nov 2017 00:09:56 GMT

May be this

Avg(Aggr(Sum(Deposits), Month, Date))

Re: Set analysis expression to calculate the average

Anonymous — Mon, 20 Nov 2017 01:06:54 GMT

Thank you!

Re: Set analysis expression to calculate the average

Anonymous — Tue, 21 Nov 2017 03:30:43 GMT

Hello,

The formula works but I am unable to use it in my pivot summary table. Do you know why this could be?

The expression that i am using is this if(Plan='Average',Avg(Aggr(Sum(Deposits), Month, Date)))

Thanks

Re: Set analysis expression to calculate the average

Anil_Babu_Samineni — Tue, 21 Nov 2017 07:24:50 GMT

use this?

Avg(Aggr(If(Plan = 'Average', Sum(Deposits)), Month, Date))

Re: Set analysis expression to calculate the average

sunny_talwar — Tue, 21 Nov 2017 09:45:21 GMT

I would suggest you to use set analysis like this

Avg({<Plan = {'Average'}>}Aggr(Sum({<Plan = {'Average'}>}Deposits), Month, Date)))

and you might also need to add Plan to the Aggr() function here

Avg({<Plan = {'Average'}>}Aggr(Sum({<Plan = {'Average'}>} Deposits), Month, Date, Plan)))