topic Re: Sum record before date with several selected dates in QlikView

Sum record before date with several selected dates

Antoine5531 — Mon, 19 Jul 2021 12:52:11 GMT

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

Re: Sum record before date with several selected dates

Kushal_Chawda — Mon, 19 Jul 2021 13:16:02 GMT

@Antoine5531 try with double quotes

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

Re: Sum record before date with several selected dates

Antoine5531 — Mon, 19 Jul 2021 13:35:06 GMT

Thanks for the reply.

I tried your way but only get 0.00€ everywhere.

Re: Sum record before date with several selected dates

Kushal_Chawda — Mon, 19 Jul 2021 14:21:43 GMT

@Antoine5531 what do you want to achieve?

Re: Sum record before date with several selected dates

Antoine5531 — Mon, 19 Jul 2021 14:35:38 GMT

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.

Re: Sum record before date with several selected dates

Kushal_Chawda — Mon, 19 Jul 2021 14:41:32 GMT

@Antoine5531 try below expression

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

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

SUM(MBELE))

Re: Sum record before date with several selected dates

Antoine5531 — Mon, 19 Jul 2021 14:48:54 GMT

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

Thanks a lot