Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ?
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;
As below
=sum( Aggr( if ( Year(FCDATE) = Min(Year(FCDATE)), FCTOTAL_HT ),RS_Client ))
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 :
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 ?
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;
Greate Vinetth !
I've tried with total, but it were not at the good place.
Thx a lot !
JF