Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try like below
=Sum(Aggr( If(Data=Date(Max(TOTAL <ID>Data),'DD/MM/YYYY'),sum(Importo),0), ID, Date))
Try like below
=Sum(Aggr( If(Data=Date(Max(TOTAL <ID>Data),'DD/MM/YYYY'),sum(Importo),0), ID, Date))
Nice @MayilVahanan.
But @michele_stenico , you should think about passing that kind of complexity to the script
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
@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