I have got a pretty straight forward table. It contains our item ledger entrys. So every time a certain item is added or taken from our inventory a item ledger entry with the corresponding date is entered with the value 1 or -1. The sum of this values, stored in the field Outstanding Quantity, represents the inventory up until that moment.
I want to add a time dimension to the inventory. So if I select for example March 2009 I need to sum everything in the field Outstanding Quantity from the startdate of the table until 31-3-2009. A normal sum would just sum this field for every entry in March but this will not give the correct inventory.
Is there a function to do this or do I need to do some data manipulation?
You should be able to handle this with Set Analysis. You want everything prior to and including the selected month.
I set up some data to test and I had a date and the month of that date stored as a number. I set up a variable: vMonthSel = '=GetFieldSelections(Month)'.
Then in my formula, I could use:
Sum({1<Month = {'<= $(#vMonthSel)'}>} Amount)
The {1 means ignore selections and then the rest of the set expression requires that the month be less than or equal to the selected month (via the variable). This breaks when multiple months are selected, but you should be able to handle that with a little tweaking if that is a requirement.
I hope I understand your question correctly, but the way I see it you have 3 options:
1. You could add another expression, same as the "Outstanding Quantity" expression, but assign FULL ACCUMULATION on the expressions tab. This way you'll have an accumulated balance. Of course if you select Dec-08,Jan-09 then it will only calculate those two months.
2. I suppose that a set analysis would be what you're looking for: sum({1<LedgerDate<={20090331}>}"Outstanding Quantity"). This would give you the sum of all since beginning to 20090331 no matter what is selected.
