Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
Valued Contributor III

Re: How to range sum values?

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

Hope this helps.

Not applicable

Re: How to range sum values?

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

Re: How to range sum values?

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
Valued Contributor III

Re: How to range sum values?

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

Re: How to range sum values?

Thanks krishnamoorthy.

That is a great help. Bravo!

Not applicable

Re: How to range sum values?

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
Honored Contributor II

Re: How to range sum values?

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?

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?

Life is so rich, and we need to respect to the life !!!
Community Browser