Select value of Last transaction for each product in a range of periods

[pablolabbe]

I have attached a sample application.

My problem is : selecting a range of dates (data_ocorrencia) the table need to show the records as show on right tables. I have detached the table to show the desired result. I couldn't achieve this using set analysis and IF conditions.

The objective is simple:

For each prd_id (product), show me the value (valor) of the last transaction based on nro_ocorrencia (occurrence number) according to a selected range of dates or all dates.

[pablolabbe · Accepted solution]

Problem solved without set analysis, only a simple if with aggr function:

sum(if (ocorr_id= aggr(nodistinct max(ocorr_id),prd_id), valor))