sum of import in the last date for each ID

[michele_stenico]

Hi guys,

I am trying to resolve a problem (without using load script). I have to analyze my customers and for each I need to calculate the values of sales at last date.

Es.

LOAD * INLINE
[
PROG,ID, Data, Importo
1,1,'01/01/2010',10
2,1,'01/02/2020',15
3,2,'01/01/2010',20
4,2,'01/05/2020',30
5,3,'01/02/2010',10
6,3,'15/01/2020',15
7,3,'15/01/2020',20
8,4,'10/01/2020',25
];

Where ID 's are my customer, I have to obtain  a table with this data

ID, Data, Importo
1,'01/02/2020',15
2,'01/05/2020',30
3,'15/01/2020',35
4,'10/01/2020',25

Please notice that I have keep the more recent value of each customer and for ID 3 I have added up the two values at 15/01/2020, it means 15+20=35.

I tried with this formula: If(Data=Date(Max(TOTAL <ID>Data),'DD/MM/YYYY'),sum(Importo),0) that works when I keep the max details with data fields, but doesn't work when I try to aggregate as in th target example. I have also tried with aggr function.

Could you give to me  any suggestions ?

Thanks

[MayilVahanan · Accepted solution]

Hi @michele_stenico 

Try like below

=Sum(Aggr( If(Data=Date(Max(TOTAL <ID>Data),'DD/MM/YYYY'),sum(Importo),0), ID, Date))

[QFabian]

Nice @MayilVahanan.

But @michele_stenico , you should think about passing that kind of complexity to the script

[zhaofeng]

Hi Michele

I agree with what @QFabian  said.

I think this caculate logic should be write in the load script.The app performance will be declined due to the complex expression in the chart if the data amount is very big

[michele_stenico]

@MayilVahanan. real nice solution. It works. 

@QFabian & @zhaofeng, I agree with you in normal situation, but some times I need to build formula in Publicated App, thus I have not the possibility to modify Load script.

Thanks to all

Michele