Sum in expression

Fri, 23 Sep 2016 13:26:28 GMT

Hi Experts,

I have the follw table:

MM_YEAR	MM_DATA_MOV	MM_CYCLE	MM_CODE	MM_DESCRIPTION	MM_QTY	MM_WARE	MM_AMOUNT
2014	01/12/2014 00:00	ACQ	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	2	CO	80
2015	08/04/2015 00:00	LOG	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	1	CO	0
2013	01/01/2013 00:00	LOG	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	10	CO	400
2014	19/03/2014 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80
2014	24/04/2014 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80
2014	08/05/2014 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80
2014	14/05/2014 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	68
2014	24/09/2014 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	68
2014	01/10/2014 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	72
2015	19/05/2015 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	100
2015	18/11/2015 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80
2013	25/11/2013 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80
2013	04/12/2013 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80
2013	09/12/2013 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80
2013	11/12/2013 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80
2014	18/12/2014 00:00	VEN	ABS002	ESTINTORE POLVERE PER ADDESTRAMENTO	-1	CO	80

i wish create a Pivot table where in dimension I have:

MM_CODE, MM_DESCRIPTION, MM_WARE, MM_YEAR

In Expression I wish sum the mm_qty field where the YEAR(MM_DATA_MOV) <= MM_YEAR.

So the final result should be that: for each mm_code and mm_ware and for every year I have the final sum from the first mm_data_mov .

I tried write expression like these:

=Sum(if (YEAR(MM_DATA_MOV) <= MM_YEAR, MM_QTY,0)) but don't work.

Can you help me?

Massimo

Re: Sum in expression

sunny_talwar — Fri, 23 Sep 2016 15:58:36 GMT

This seems to be working for me

=Sum(If(Year(MM_DATA_MOV) <= MM_YEAR, MM_QTY))

Mon, 26 Sep 2016 09:57:55 GMT

thanks for the reply, but probably I explained badly.

I wish that the expression there is always the balance of the amount.

Then:

2013 = 6

2014 = 1

2015 = 0

Massimo

sunny_talwar — Mon, 26 Sep 2016 17:31:36 GMT

Still not 100% sure, but this?

=RangeSum(Above(TOTAL Sum(If(Year(MM_DATA_MOV) <= MM_YEAR, MM_QTY)), 0, RowNo(TOTAL)))