2 Replies Latest reply: Jul 20, 2017 9:19 AM by Pim van der Kolk RSS

    Only show the totals of a variable and deduct it in the first row after the total has been reached

    Pim van der Kolk

      Hi there,

       

      I'm trying to build a table with multiple invoices of hours spent per month. Some invoices are completed after more than one month and I only want to see the total amount in the month of completion.

      With the help of the first formula below I only get the number of hours spent in the invoice month, the month of completion. The second formula shows the total amount of hours spent in the invoice month, together with the previous months where a part of the invoice is booked (which is what I want), but obviously accumulates the hours of all the invoices (which is what I don't want).

       

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

       

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

       

      So what I need is a table which shows the total hours of an invoice in the month of the invoiced month and nothing in the months before in case a part of that invoice was already booked. Next to this, the first month after such an invoice the hours need to be deducted from the column.

       

      If it's not clear enough, let me know and I'll set up a demo.

       

      Thanks in advance,

      Pim