topic Re: expression wrong correct me? in QlikView

expression wrong correct me?

Sat, 20 Dec 2014 07:55:13 GMT

sum({<year=,monthnum=}>}sales/count(monthnum)

here

year:

2000

2001

2002

2003

monthnum:

i want calculate the avg of all months , pls tell me above expression correct or not ?

if wrong pls correct me and if u have another way to calculate pls tell me that expression

Re: expression wrong correct me?

its_anandrjs — Sat, 20 Dec 2014 08:07:12 GMT

Hi,

You can try this

sum({<year=,monthnum= >} sales) / count(DISTINCT monthnum)

But tough to tell works if possible provide any sample file with few rows.

Regards

Anand

Re: expression wrong correct me?

Sat, 20 Dec 2014 08:13:47 GMT

for Total sales i have written like this = sum({<year=,monthnum= >} sales)

and for avg i have written like this but i am getting same output for both

sum({<year=,monthnum= >} sales) / count(DISTINCT monthnum)

Re: expression wrong correct me?

its_anandrjs — Sat, 20 Dec 2014 08:18:35 GMT

Hi,

What is your month wise average result let assume this data

year	monthnum	sales
2000	5	14578
2001	6	3467
2002	7	3468
2003	8	47980

Regards

Anand

Re: expression wrong correct me?

Sat, 20 Dec 2014 08:19:51 GMT

INLINE [

Date, prod, Sales, Monthnum, Y

02/08/2012, E8, 208, 08, 2012

01/08/2012, E7, 102, 08, 2012

12/07/2012, A1, 100, 07, 2012

12/09/2012, A4, 106, 07, 2012

01/05/2012, A2, 21, 05, 2012

01/06/2013, B2, 90, 06, 2013

02/02/2013, B3, 29, 02, 2013

01/06/2011, C3, 20, 06, 2012

20/12/2011, D4, 200

24/09/2011, D5, 230

20/03/2012, E5, 110

20/03/2012, E6, 900

08/12/2014, A7, 211, 05, 2014

19/11/2014, B7, 901, 06, 2014

17/12/2014, B8, 291, 02, 2014

18/12/2014, C9, 201, 06, 2014

];

Re: expression wrong correct me?

its_anandrjs — Sat, 20 Dec 2014 08:40:38 GMT

Hi,

Try this

Dim1:- Year

Dim2:- monthnum

Expre:-

sum( {<Year=, Monthnum=>}

Sales) / Count(Monthnum)

And for blank value remove from the list box

And let me know you have few rows blank values you want to remove them or keep as it is because it has no details.

Regards

Anand

Re: expression wrong correct me?

MK_QSL — Sat, 20 Dec 2014 10:15:11 GMT

You can get the average by

SUM(Sales) / COUNT(Distinct NumMonth&Year)

Avg(Aggr(SUM(Sales),NumMonth,Year))