Incomplete sum() on aggr function

[PWJ67]

Hi all,

In my chart bar, my dimension is the year of sales (year(FCDATE)).

I try to calculate for each year, the sales of the new client for this year.

• FCDATE = date of sale
• RS_Client = the client
• FCTOTAL_HT = the amount  of each sale

The aggr in this formula returns only one FCTOTAL_HT by Client, and I don't find how to change this :

sum( if(aggr(min(year(FCDATE)),RS_Client)=year(FCDATE) , FCTOTAL_HT))

NB : using "nodistinct" in the aggr() function did not work.

Any idea ?

[vinieme12]

As below

=sum(  Aggr( if ( Year(FCDATE) = Min(Year(FCDATE)), FCTOTAL_HT ),RS_Client ))

[PWJ67]

Thx Vineeth but it does not work, the formula returns 0 €.

Here is an example from 1 client. The formula have to return the sum of the 2 first lines : 465 x 2 = 930.

I tried many solutions, but only had 3 results :

• 0 € 
• the total of all lines
• the first line alone ; 465

FCNUMFACT	FCDATE	FCTOTAL_HT
G 18191702	07/09/2015	465 €
G 20151158	11/09/2015	465 €
Z 20160149	31/01/2016	30 €
G 20160858	30/06/2016	715 €
DI09194285	21/09/2016	265 €
F 09192256	21/09/2016	1 430 €
F 20161296	23/09/2016	1 430 €
DI20161403	30/09/2016	265 €
G 20161665	30/11/2016	350 €
G 20170651	31/05/2017	85 €
G 20170811	30/06/2017	395 €
V 20170812	30/06/2017	250 €
V 20180178	26/02/2018	250 €
V 20180257	28/02/2018	140 €
V 20180368	29/03/2018	950 €
G 20180641	06/06/2018	85 €
G 20181277	31/10/2018	100 €
V 20181278	31/10/2018	950 €
F 20191842	21/03/2019	1 061 €
V 20191960	15/04/2019	6 535 €
V 20192410	31/07/2019	1 000 €
V 20192845	29/11/2019	750 €
V 20200473	30/06/2020	750 €
F 20200609	30/07/2020	464 €
V 20201059	30/11/2020	750 €
V 20210572	31/05/2021	1 000 €
V 20211354	26/11/2021	750 €
V 20220575	31/05/2022	1 000 €
V 20220682	30/06/2022	150 €
V 20221291	30/11/2022	750 €

I tried adding FCNUMFACT or FCDATE as 2 level of aggr() : failure

Other try returns nested errors.

Othe ideas ?

[vinieme12 · Accepted solution]

as below

Chart Dimension:

RS_Client

Chart Measures:

=min(total <RS_Client> year(FCDATE))  

=sum(aggr(if(Year(FCDATE)=min(Total <RS_Client> year(FCDATE)),FCTOTAL_HT),RS_Client,FCDATE))

temp:
load 'A' as RS_Client,FCNUMFACT,date#(FCDATE,'DD/MM/YYYY') as FCDATE,FCTOTAL_HT inline [
FCNUMFACT,FCDATE,FCTOTAL_HT
G 18191702,07/09/2015,465
G 20151158,11/09/2015,465
Z 20160149,31/01/2016,30
G 20160858,30/06/2016,715
DI09194285,21/09/2016,265
F 09192256,21/09/2016,1 430
F 20161296,23/09/2016,1 430
DI20161403,30/09/2016,265
G 20161665,30/11/2016,350
G 20170651,31/05/2017,85
G 20170811,30/06/2017,395
V 20170812,30/06/2017,250
V 20180178,26/02/2018,250
V 20180257,28/02/2018,140
V 20180368,29/03/2018,950
G 20180641,06/06/2018,85
G 20181277,31/10/2018,100
V 20181278,31/10/2018,950
F 20191842,21/03/2019,1 061
V 20191960,15/04/2019,6 535
V 20192410,31/07/2019,1 000
V 20192845,29/11/2019,750
V 20200473,30/06/2020,750
F 20200609,30/07/2020,464
V 20201059,30/11/2020,750
V 20210572,31/05/2021,1 000
V 20211354,26/11/2021,750
V 20220575,31/05/2022,1 000
V 20220682,30/06/2022,150
V 20221291,30/11/2022,750
];

Concatenate(temp)
load 'B' as RS_Client,FCNUMFACT,date#(FCDATE,'DD/MM/YYYY') as FCDATE,FCTOTAL_HT inline [
FCNUMFACT,FCDATE,FCTOTAL_HT
Z 20160149,31/01/2016,30
G 20160858,30/06/2016,715
DI09194285,21/09/2016,265
F 09192256,21/09/2016,1 430
F 20161296,23/09/2016,1 430
DI20161403,30/09/2016,265
G 20161665,30/11/2016,350
G 20170651,31/05/2017,85
G 20170811,30/06/2017,395
V 20170812,30/06/2017,250
V 20180178,26/02/2018,250
V 20180257,28/02/2018,140
V 20180368,29/03/2018,950
G 20180641,06/06/2018,85
G 20181277,31/10/2018,100
V 20181278,31/10/2018,950
F 20191842,21/03/2019,1 061
V 20191960,15/04/2019,6 535
V 20192410,31/07/2019,1 000
V 20192845,29/11/2019,750
V 20200473,30/06/2020,750
F 20200609,30/07/2020,464
V 20201059,30/11/2020,750
V 20210572,31/05/2021,1 000
V 20211354,26/11/2021,750
V 20220575,31/05/2022,1 000
V 20220682,30/06/2022,150
V 20221291,30/11/2022,750
];

exit Script;

[PWJ67]

Greate Vinetth ! 

I've tried with total, but it were not at the good place.

Thx a lot !

JF