topic Re: Accumulation per invoice number in QlikView

Accumulation per invoice number

thanhphongle — Thu, 09 Nov 2017 16:03:33 GMT

!Hello Community,

is it possible to define the accumulation per invoice number?

Example

invoice_no	payments	accumulation
1	10	10
1	20	30
1	30	70
2	100	100
2	200	300
2	300	700

This is what QV returns when I set the accumulation active in the expression tab

invoice_no	payments	accumulation
1	10	10
1	20	30
1	30	70
2	100	170
2	200	370
2	300	770

Re: Accumulation per invoice number

sunny_talwar — Thu, 09 Nov 2017 16:06:27 GMT

Try using an expression like this

RangeSum(Above(Sum(payments), 0, RowNo()))

I am assuming that you have more than 1 dimension in your chart because how else will you see repeated invoice_no

Re: Accumulation per invoice number

Anil_Babu_Samineni — Thu, 09 Nov 2017 16:08:01 GMT

Perhaps this

RangeSum(Above(Sum(payments), 0, RowNo(TOTAL)))

Re: Accumulation per invoice number

sunny_talwar — Thu, 09 Nov 2017 16:17:01 GMT

I don't think TOTAL is needed within RowNo() here

Re: Accumulation per invoice number

thanhphongle — Thu, 09 Nov 2017 16:23:38 GMT

It still continues to accumulate in a recursive way through all invoice number

Re: Accumulation per invoice number

thanhphongle — Thu, 09 Nov 2017 16:24:27 GMT

I mean it doesnt restart the accumulation for the next invoice numeber

Re: Accumulation per invoice number

sunny_talwar — Thu, 09 Nov 2017 16:29:39 GMT

What all dimensions do you have in your chart?

Re: Accumulation per invoice number

thanhphongle — Thu, 09 Nov 2017 16:34:50 GMT

invoice_no and start_date
I added the expression dual(invoice_no, start_date) in the dimension, so i can see duplicated invoice numbers, as every invoice number has another payment

Re: Accumulation per invoice number

sunny_talwar — Thu, 09 Nov 2017 16:36:12 GMT

May be this

Aggr(RangeSum(Above(Sum(payments), 0, RowNo())), invoice_no, start_date)

Re: Accumulation per invoice number

thanhphongle — Thu, 09 Nov 2017 16:40:22 GMT

I shared my qv-file to you !

Re: Accumulation per invoice number

sunny_talwar — Thu, 09 Nov 2017 16:41:10 GMT

Where is it shared? I don't see it?

Re: Accumulation per invoice number

el_aprendiz111 — Thu, 09 Nov 2017 22:02:45 GMT

Hi,

=Aggr(RangeSum(Above(sum(price),0,RowNo())), contract_no,start_date)

Re: Accumulation per invoice number

sunny_talwar — Thu, 09 Nov 2017 23:27:06 GMT

Seems to be working? What is wrong??? PFA

Re: Accumulation per invoice number

thanhphongle — Fri, 10 Nov 2017 11:01:51 GMT

Thank you so much, finally it works !!

Re: Accumulation per invoice number

sunny_talwar — Fri, 10 Nov 2017 12:10:11 GMT

I must confess thanhphongle that I am very disappointed and discouraged (no offence to you my friend el_aprendiz111)... If you see that the only difference between the two expression (one that worked and the one that I gave initially) was the name of the fields that you were using in your chart

I gave this

vs this is what finally worked

I used the fields which you told me in your initial responses.... I am not sure if I should have been smart enough to know your fields or you should have been smart enough to replace your actual fields in the expression I gave.

On top of it, you rubbed on my face by saying "Thank you so much, finally it works!!" as if what I gave never really worked....

Anyways, I know what I have to do when I see your future posts

Best,

Sunny

Re: Accumulation per invoice number

thanhphongle — Fri, 10 Nov 2017 15:14:04 GMT

Hey Sunny Talwar, I m sorry about not giving you the right response. I know you 've help me so much so far. I was quite busy this morning, so I actually forgot to realised that you help me most in this case so far. I ll edit the post. Hope this is ok for you

Re: Accumulation per invoice number

sunny_talwar — Fri, 10 Nov 2017 15:21:56 GMT

thanhphongle‌ the intention was not to take anything away from el_aprendiz111 (in fact it would be unfair to give me the correct response after I messaged you and I would request to give it back to him). But the intention was to let you know that you need to at least acknowledge the fact that I gave you the correct response earlier, but you just didn't apply it for your case. I am not here to gather points, but to help people (not getting paid for this). But it tends to get annoying that after so much effort, people don't even want to acknowledge your contribution.

Re: Accumulation per invoice number

thanhphongle — Tue, 14 Nov 2017 10:51:04 GMT

I'm sorry about it. I'm really thankful for help. The next time I will definetly acknowledge the contritbution of people's help.