Pivot Table - Start Of Month Warehouses Balance

[matancha]

Hello all.

Please look on the attached file.

In this file I have: Parts, Orders for parts, current warehouse balance for each part, due-date calendar, and table with accumulated order quantity for each part for each order-row.

I need to calculate the warehouses balance on the start of the month, i.e - i need to "Copy" the previous 'EOM Warhs Balance' column,

But  - without use functions like - "Before" & "After", so if user select a specific months, he will get real data (not depend on the previous

columns) .

The 'EOM Warhs Balance' expression is:

Sum(WarehousesBalance.Balance) - FirstSortedValue(AccumulateBalanceOpenOrders.AccTBalance, -1 * AccumulateBalanceOpenOrders.RowNo)

In Short:

and when choosing YM =  '2014-10' the table will show:

I think that a combination of 'Firstsortedvalue()' and 'Aggr()' functions will solve this problem but I don't know how....

Hope I was able to clearly explain the problem...

Many thanks for any help -

Matan.

[sujeetsingh]

Matan ,

It is just due to the scene that you have only one expression giving value for a time period

[sujeetsingh]

See the sample you have posted

[sujeetsingh]

Mantan,

You have to use Peek function in script bashed on the date and derive the previous values of the field to be used in the expression and then use it .

[matancha]

sujeetsingh - thanks for your quick answers,

but i didn't see any changes in my expressions.

I only saw change in table type (pivot --> straight).

Matan

[matancha]

Can you show me an example?

I couldn't understand what you're saying.

Note that the time dimension is cyclic: YM | YQ | Year | Date ...

Thanks again...

Matan.