Calculate outgoing balance for products with different max dates within a month

[Anonymous]

Hi,

I'm trying to calculate the outgoing balances for the chosen month for different products in the same pivot table.

The problem is that the products have different max dates.

Sum({$<%Date={'$(vMaxDate)'}>}DELIVERYQTY)

And the variable vMaxDate is: =Max({$<Type={'D'}>}%Date)

The products might have max dates:

Product A = Max date = 2016-02-29

Product B = Max date = 2016-02-27

One solution might be to create a variable that is a nested if or maybe in the script find the maxDate for each product.

vMaxDateA =Max({$<Product={'A'},Type={'D'}>}%Date)

vMaxDateB =Max({$<Product={'B'},Type={'D'}>}%Date)

and so on but the problem will be if new products arrive and to maintain the nested if :I.

Any ideas?

[Gysbert_Wassenaar]

sum(aggr(FirstSortedValue(DELIVERYQTY, -%Date),Product))

[Anonymous]

Thanks Gysbert,

I was actually just experimenting with firstsortedvalue and aggr.

I tried your expression but it returns 0 on all the rows.

I get some results with this, not the correct results though .

=FirstSortedValue( aggr(sum(DELIVERYQTY),Product,%Date), -aggr(%Date,Product,%Date) )

[Anonymous]

Still haven't solved this, any ideas?

Gysbert, haven't got the expression with firstsortedvalue to work yet.