Sum record before date with several selected dates

[Antoine5531]

Hello

I'm having a hard time to adapt the expression of a pivot table to work with multiple selections.

Here is an image of my table : 

Here is the 'Antérieur' expression (CDELEI = YEAR and MBELE = values to sum) :

SUM({<[CDELEI]={'$(=Max([CDELEI]))'}>}MBELE)

As you can see it only work on the latest year (2017) but not the previous years because of the max function. I'm trying to correct that.

I figured out this expression to return me the year of the column :

SubField(GetFieldSelections(CDELEI, '|'), '|', -ColumnNo(Total))

But combining these two expression dosn't seems possible, I tried many things like :

SUM({<[CDELEI]={'$(=Max(SubField(GetFieldSelections(CDELEI, CHR(39)|CHR(39)), CHR(39)|CHR(39),-ColumnNo(Total))))'}>}MBELE)

Is it because of the quotes inside quotes or am i doing it wrong ? (loosing my mind)

Thanks

[Kushal_Chawda]

@Antoine5531  try with double quotes

SUM({<[CDELEI]={"$(=Max(SubField(GetFieldSelections(CDELEI, CHR(39)|CHR(39)), CHR(39)|CHR(39),-ColumnNo(Total))))"}>}MBELE)

[Antoine5531]

Thanks for the reply.

I tried your way but only get 0.00€ everywhere.

[Kushal_Chawda]

@Antoine5531  what do you want to achieve?

[Antoine5531]

For each selected year, I have a column with the year (CDELEI) being displayed in the table. I would want my expression to return the sum of all the records (MBELE) before that year.

I managed to do it that way :

SUM({<[CDELEI]={'$(=Max([CDELEI]))'}>}MBELE)

 But when multiple years are selected, the expression only work on the latest year and return 0.00 for the other (because of the max). I'm trying to correct that so that I can compare several years in my table.

[Kushal_Chawda · Accepted solution]

@Antoine5531  try below expression

=if(getselectedcount([CDELEI])=0,

SUM({<[CDELEI]={'$(=Max([CDELEI]))'}>}MBELE),

SUM(MBELE))

[Antoine5531]

It seems good, I'll check the numbers toomorow.

Thanks a lot