Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

expression wrong correct me?

Hi

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

here

year:

2000

2001

2002

2003

monthnum:

05

06

07

08

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

6 Replies
its_anandrjs

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

Not applicable
Author

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)

its_anandrjs

Hi,

What is your month wise average result let assume this data

   

yearmonthnumsales
2000514578
200163467
200273468
2003847980

Regards

Anand

Not applicable
Author

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

    ];

its_anandrjs

Hi,

Try this

Dim1:- Year

Dim2:- monthnum

Expre:-

sum( {<Year=, Monthnum=>}Sales)  / Count(Monthnum)

Charts.png

And for blank value remove from the list box

BlankList.png

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

MK_QSL
MVP
MVP

You can get the average by

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

Or

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