Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
michele_stenico
Partner
Partner

sum of import in the last date for each ID

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

 

 

 

 

 

Labels (2)
1 Solution

Accepted Solutions
MayilVahanan

Hi @michele_stenico 

Try like below

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

MayilVahanan_0-1604544467748.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

4 Replies
MayilVahanan

Hi @michele_stenico 

Try like below

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

MayilVahanan_0-1604544467748.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

QFabian
Specialist II
Specialist II

Nice @MayilVahanan.

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

QFabian
zhaofeng
Partner
Partner

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
Partner
Partner
Author

@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