Hello, I have the following question: I am reporting on a table with open invoices the invoice has a GL bookingdate. I also have a table with all dates in it. What I would like to do is a historical open invoice report using the following:
sum(invoiceamount) but only when isnull(getfieldselections(bookingdate)) or bookingdate <= getfieldselections(bookingdate).
The latter 2 condtions I would like to wrap in a set analyses. Is this possible? I have been testing and trying for a while now, without much luck sofar. Any help would be appriciated.
I am sorry, but after copying your solution I got extreme numbers. Even after isolating the invoice table I got the same results. One of the original invoices was for EUR 20.483,20. In the changed column with the formula it now shows as EUR 22.449.587,20. The same happens to the other 3 invoices.
I do not think that this will be a solution that is going to work. It also takes approx 40 seconds to calculate totals and we are only talking about a table wih a little over 140000 rows.
I did find some problems in the construction of the data. I have been working on this a bit more and I now believe that the proposed solution might work. I will be continuing work on tuesday.
I have added a new test example with more data. I have also selected the specific customer of the previous example. You can see there that the total open amount is much more then just the 3 open invoices. I am not sure what is going on. Txs,André
What I am trying to achieve with this is a flex debtors aging report. So, if the user does not select a date in the most left date selection, it should show all current open invoices. As soon as the user selects a date, the report should figure out which invoices were open at the time of that date. So the payment should not have been done yet (DBetaaldatum < selected date) and the amount paid should be less than the invoice amount.
In the other graphs it should only show data that is applicable to the selections applied. (invoices and payments). I was thinking of building set analyses sums for this, but looking at the selection criteria that need to be worked with I am not sure that that would be possible.
