Help with set expression used with stock level and stock movements

[kbryndum]

Hi,

Can anyone help me with a set expression, that only shows the value of the max(Date) of a table that consist of the dimension AsOfDate.

The purpose is to show the period movement based on Date, but in the table consisting of AsOfDate dimension and the measure Qty_as_of_date .

I have the period movements for each Date:

I have also Qty for each AsOfDate (stock level):

Now I want to show both measures in the same table, but only with AsOfDate as a dimension. The AsOfDate consist of all dates where Date <= AsOfDate, so for each AsOfDate there is one or more Dates linked to it.

Basically I want to use the Max(Date) for each AsOfDate in a expression like:

sum({$<Date={"$(=max(Date))"}>} TOTAL <[AsOfDate]> Period_QTY)

But this gives only a value for max date of the total application. I need max date used for each record in the table. See attached example.

Any help is much appreciated.

Thanks.

/Kim

[swuehl · Accepted solution]

Maybe like

=sum(total<AsOfDate> aggr(if(Date =max(total<AsOfDate> Date), Period_QTY), AsOfDate,Date))

Hope this helps,

Stefan

[kbryndum]

Great! That's is exactly what I wanted.

I have been looking at creating a set expression, but that might have been the wrong approach.

Thank you so much!

/Kim

[swuehl]

There is even a much simpler expression:

=FirstSortedValue(total<AsOfDate> Period_QTY, -Date)

[kbryndum]

That's nice, but if I had more dimensions than the date, I can't use it.