Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pvanderkolk
New Contributor III

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

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

2 Replies
arvind_patil
Valued Contributor II

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

Hi Pim,

Please provide the sample Data. as well as expected result.

Thanks,

Arvind Patil

pvanderkolk
New Contributor III

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

Hi Arvind,

See the attached data in the original post. It's about the green highlighted column.

Thank you