topic Re: How to range sum values? in QlikView

How to range sum values?

Fri, 11 Jan 2013 23:49:46 GMT

How to sum values per cut-off?

Say, I have this list:

Date	Amount
1/1/2013	500
1/3/2013	400
1/4/2013	300
1/9/2013	600
1/10/2013	1,000
1/12/2013	250

How can I get the following values, if

(1) When my date is 1/1/2013, the answer should be 500

(2) When date selected is 1/2/2013, the answer is still 500

(3) When date is 1/3/2013, the answer is now 900, because you should sum the previous amount

(4) When date is 1/4/2013, the answer is now 1,200 - because you should always sum previous amount

so on and so forth.

Any one can help? Thanks.

Re: How to range sum values?

nagaiank — Sat, 12 Jan 2013 00:19:46 GMT

One wayof doing what you want is in the attached qvw file.

Hope this helps.

Re: How to range sum values?

Sat, 12 Jan 2013 01:04:31 GMT

Can you also do that in script? Because I need to fix it, it should be that, regardless of selecting dates, the rangesum will always be same.

Date	Amount	RANGESUM
1/1/2013	500	500
1/3/2013	400	900
1/4/2013	300	1,200
1/9/2013	600	1,800
1/10/2013	1,000	2,800
1/12/2013	250	3,050

So When I select only 1/3/2013, then RANGESUM will always be 900 and will never be changed to 400. Please help me. Thanks.

Re: How to range sum values?

Sat, 12 Jan 2013 01:41:44 GMT

Hi,

In order to make a full accumulation in the script first you need to order the table by the date, after that you can use de peek function, this function returns the previous record read of a field, so the script should be something like this:

load Date

Amount

peek('Amount') + Amount as AmountAccum

from OrderTable

Hope this helps

Regards!

Re: How to range sum values?

nagaiank — Sun, 13 Jan 2013 16:07:02 GMT

I have modified the application to calculate the cumulaive amount in the script so that the date selection will not have any effect on the cumulative value. Hope this meets your needs.

Re: How to range sum values?

Mon, 14 Jan 2013 01:06:57 GMT

Thanks krishnamoorthy.

That is a great help. Bravo!

Re: How to range sum values?

Mon, 14 Jan 2013 03:09:29 GMT

Hi,

Can you help me getting the accumulation for this on the script? I can't able to do it using/referencing your latest created QVW.

Attached file is the sample file, it has 3 TEST CODES, so each should have different accumulations.

Accumulate only PRINCIPAL. No need to accumulate INTEREST.

Also try RANGESUM if it works. Thanks for the help in advance.

Re: How to range sum values?

er_mohit — Mon, 14 Jan 2013 06:53:41 GMT

try this

rangesum(above(sum(Amount)

or you can use this expression if there is date condition like you only commulate the amount of 2012..not 2011

then try this

rangesum(above(sum(Amount),0,rowno()))

hope it helps

Re: How to range sum values?

Anil_Babu_Samineni — Wed, 20 Jul 2016 08:27:43 GMT

If you are fine, Please flag the Correct answer.

Or else create one more expression with Sum(Amount) and then try to put that expression on the left of down Accumulation and please choose the Full accumulation then you will get as per your requirement.

Please close this thread so that Freshers can easily understood?