topic Re: Only show total of each unit of a field and deduct when total of unit is reached in QlikView

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

pvanderkolk — Fri, 21 Jul 2017 07:22:12 GMT

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

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

Anil_Babu_Samineni — Fri, 21 Jul 2017 07:25:55 GMT

I think you need Master calendar which is missing over here? Do you have master calendar in your application?

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

pvanderkolk — Fri, 21 Jul 2017 07:32:22 GMT

Yes, the master calendar is in the script. The booked months and invoice months are already linked. But since this is a new tab in my app I prefer not to adjust the script anymore and solve this in the expressions of the straight table. I only want to show the historical data in here, when an invoice is actually been invoiced instead of the partial sums each month.

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

Anil_Babu_Samineni — Fri, 21 Jul 2017 07:40:53 GMT

Front end also i think it's possible. Because, Once upon a time stalwar1‌ solves that. And i think the way front end is not good because we need to handle expression as calc. Some how, Can we know why you don't prefer to change in script level. If you have Invoice Month in the script which covers all months i think you will need single line only.

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

pvanderkolk — Fri, 21 Jul 2017 07:49:20 GMT

I'm not cheering for front end either, but in this case the script is already quite complex. I don't want to take the risk that it influences tables in other tabs of this app.

Do you happen to have a link with the solution of Sunny Talwar?