4 Replies Latest reply: Jul 21, 2017 3:49 AM by Pim van der Kolk RSS

    Only show total of each unit of a field and deduct when total of unit is reached

    Pim van der Kolk

      Hi there,

       

      I'm trying to create a table with some invoices that is showing me only the total amount in the month this amount has been reached. Next to this, when such an invoice is complete, the amount of this invoice has to be disappeared in the next month.

      So for instance, if you have four invoices like below:

       

      Invoice month     Booking month       Hours spent

      09-'16                  09-'16                     20

       

      Invoice month     Booking month       Hours spent

      12-'16                  09-'16                     12

                                  11-'16                     25

                                  12-'16                      9

       

      Invoice month     Booking month       Hours spent

      12-'16                  12-'16                     23

       

      Invoice month     Booking month       Hours spent

      01-'17                  12-'16                     2

                                  01-'17                     15

       

      I only want to see the total of an invoice in my straight table (so 20 hours in September '16, 46 hours + 23 hours in December '16 and 17 hours in January '17; 0 hours in the booking months which aren't equal to the invoice month). Also, after one has been invoiced it needs to be depleted from the total in the table, as shown below:

       

      Booking month     Hours spent

      09-'16                   20

      10-'16                    0

      11-'16                    0

      12-'16                   69

      01-'17                   17

       

      With the help of the first formula below I only get the number of hours spent in the invoice month, so not the total. The second formula shows the total amount of hours spent in the invoice month (which is what I want). However, it obviously accumulates the hours of all the invoices (which is what I don't want) instead of deducting it once the invoice is being sent out.

       

      If(BookedMonthYear=InvoiceMonthYear,Rangesum(Sum({$<Conditions>} Hours)),0)

       

      If(BookedMonthYear=InvoiceMonthYear,RangeSum(Top(Sum({Conditions>} Hours),1, RowNo() -0)),0)

       

      Thanks in advance,

      Pim