Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to range sum values?

How to sum values per cut-off?

Say, I have this list:

DateAmount
1/1/2013500
1/3/2013400
1/4/2013300
1/9/2013600
1/10/20131,000
1/12/2013250

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.

8 Replies
nagaiank
Specialist III
Specialist III

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

Hope this helps.

Not applicable
Author

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.

DateAmountRANGESUM
1/1/2013500500
1/3/2013400900
1/4/20133001,200
1/9/20136001,800
1/10/20131,0002,800
1/12/20132503,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.

Not applicable
Author

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!

nagaiank
Specialist III
Specialist III

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.

Not applicable
Author

Thanks krishnamoorthy.

That is a great help. Bravo!

Not applicable
Author

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.

er_mohit
Master II
Master II

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

Anil_Babu_Samineni

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)