topic Re: Aggregating averages (Sum/Count) in QlikView

Aggregating averages (Sum/Count)

Thu, 03 Dec 2015 10:04:22 GMT

Hi all,

Please is there any reason why the following will not work? It works if I calculate the sales average (sum/count) of each product separately; however, it does not work if I try to aggregate the sales average of both products. Am I missing an aggregate function?

=Num(sum({<Sale_Date={">=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))"},ProductType={'Product 1'}>} Sales)/

Count({<Sale_Date={">=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))"},ProductType={'Product 1'}>} Sales

sum({<Sale_Date={">=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))"},ProductType={'Product 2'}>} Sales)/

Count({<Sale_Date={">=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))"},ProductType={'Product 2'}>} Sales),'##%')

Regards

Re: Aggregating averages (Sum/Count)

t_chetirbok — Thu, 03 Dec 2015 10:09:16 GMT

Hi!

What dimension are you use?

Post some example qvw, please.

Re: Aggregating averages (Sum/Count)

Thu, 03 Dec 2015 10:13:20 GMT

Hi,

The dimension is Sales

Unfortunately, I can not post the qvw.

Re: Aggregating averages (Sum/Count)

Anonymous — Thu, 03 Dec 2015 10:18:45 GMT

try this:

=Num(

((sum({<Sale_Date={'>=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))'}, ProductType={'Product 1'}>} Sales)

Count({<Sale_Date={'>=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))'},ProductType={'Product 1'}>} Sales))

(sum({<Sale_Date={'>=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))'},ProductType={'Product 2'}>} Sales)

Count({<Sale_Date={'>=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))'},ProductType={'Product 2'}>} Sales)))

,'#.##')&'%'

Re: Aggregating averages (Sum/Count)

Anonymous — Thu, 03 Dec 2015 10:19:21 GMT

also make sure Format of your expression and SalesDate should be same...

Re: Aggregating averages (Sum/Count)

Kushal_Chawda — Thu, 03 Dec 2015 10:19:56 GMT

May be like below

Num(sum({<Sale_Date={">=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))"},ProductType={'Product 1','Product 2'}>} Sales)/

Count({<Sale_Date={">=$(=Max(Sale_Date)-30)<=$(=Max(Sale_Date))"},ProductType={'Product 1','Product 2'}>} Sales),'##%')

Re: Aggregating averages (Sum/Count)

t_chetirbok — Thu, 03 Dec 2015 10:20:00 GMT

So, do you want to get a straingt table with Sales as dimension ans sum Sales as expresion?

Re: Aggregating averages (Sum/Count)

Anonymous — Thu, 03 Dec 2015 10:20:02 GMT

Sales is your dimension???

Which chart you are using?

Re: Aggregating averages (Sum/Count)

Thu, 03 Dec 2015 10:29:41 GMT

This works perfectly well.

Thanks a lot

Regards to all.

Re: Aggregating averages (Sum/Count)

Kushal_Chawda — Thu, 03 Dec 2015 10:45:58 GMT

I am glad that it is helpful.