topic Re: Average in text box in QlikView

Average in text box

Wed, 12 Oct 2011 13:22:54 GMT

Hi.

I have a pivot table contains of two demintions dates and departments and the expression is Sum() sales for everyday.

I am trying to get the average for sales per day in a text box and it is not giving me the correct data.

Average in text box

Wed, 12 Oct 2011 13:25:06 GMT

so the expression is Sum(Sales)..

I tried to add in the text box Avg(Sum(Sales)), it didnt work.

Any ideas

Re: Average in text box

its_anandrjs — Wed, 12 Oct 2011 13:27:39 GMT

Hi,

You have to use If(dates = today(),Avg(Sales) ) it will calculate average on taly basis paste this code in any text box and let me know.

you can use only this =Avg(Sales) with equal sign in text box.

Rgds

Anand

Average in text box

SunilChauhan — Wed, 12 Oct 2011 13:28:56 GMT

avg(Sales)

avg(aggr(sum(Sales),dates))

Re: Average in text box

danieloberbilli — Wed, 12 Oct 2011 13:34:56 GMT

what if you just write: avg(sales)

or try

avg(aggr(sum(sales),dimension Y))

Regards

Daniel

Re: Average in text box

its_anandrjs — Wed, 12 Oct 2011 13:42:13 GMT

Hi,

See the attached sample file

Rgds

Re: Average in text box

danieloberbilli — Wed, 12 Oct 2011 13:49:15 GMT

Everything is right. But I guess you need to add the sales per day as data in your model. This data is missing.

Until now you only can calculate avg(sales)/365 -> but I guess that is not what you need

Average in text box

Wed, 12 Oct 2011 13:51:17 GMT

The attached file is not working because it was created by another person.

If I use avg(aggr(sum(sales),dimension Y)) I get data but the data is not correct as if I do the math myself i get different Nrs.

Average in text box

SunilChauhan — Wed, 12 Oct 2011 13:55:08 GMT

avg(aggr(sum(sales),dimensioncolumn1,dimenssion column2,.......))

try using this

where dimensioncolumn1 is dimension Y

Re: Average in text box

its_anandrjs — Wed, 12 Oct 2011 14:18:02 GMT

Hi,

Use

=Avg(Sales)

=Avg( Aggr ( Avg ( Sales ),Dimension X ) )

Or if dimension is more use

=Avg( Aggr ( Avg ( Sales ),Dimension X, Dimension Y,Dimension Z ..... ) )

Rgds

Anand

Average in text box

Wed, 12 Oct 2011 14:42:19 GMT

everyone of the above is giving me different resaults but none of them is correct!!

Thank you guys for your help!

Average in text box

its_anandrjs — Wed, 12 Oct 2011 14:52:41 GMT

Hi,

I suggest try this

Avg( Aggr ( Avg ( num(Sales,'#,##0.##') ),Dimension X ) )

Rgds

Anand

Average in text box

Wed, 12 Oct 2011 14:56:21 GMT

Thank you guys! fixed

you rock!

Average in text box

its_anandrjs — Wed, 12 Oct 2011 19:17:10 GMT

Hi,

Thanks I am glad to help you.

Rgds

Anand