In the first attachment you find an example of hours spent on a project. There are two dimensions, Book Month Year and Invoince Month Year. The first expression, Chargable, is dependent on the first dimension and works well. Invoiced and Booked Off are dependent on Invoice Month Year, the second dimension. WIP is a formula consisting of the three other expressions.
The data is how the current situation is, so it's logical that WIP is zero. I'm now adding a new table with the historical data i.e. when an activity is being booked in one month but invoiced in another month, the total hours spent on that activity needs to be moved to the invoiced month (see attachment Example live data, where both lines need to be invoiced in September (142.5+142.5)).
It all works well, except for one situation; when a Book Month has both a later month as well as the month itself for the Invoice, it doesn't place the values in the right month of my straight table (see the third attachment, Straight table).
I hope you can help me with this issue. Thanks in advance!
I am not entirely sure I understood your issue... You seem to be looking to accumulate but based on month? Can you provide a sample qvw file or raw data (mocked up or real) to help understand the issue better?
Yes, but it schould only accumulate when the invoice month is later than the book month. I've added a sample data in the previous post (unfortunately couldn't provide the data with the real script). As you can see, the table in the left on top needs to be changed into the table right on the bottom. The table top right provides the formulas I'm using in the desired table.
Since the script is already quite complex I'm hoping this can be solved at front end.