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: 
cristianozilz
Partner - Contributor III
Partner - Contributor III

RangeSum Calculate Dynamic Cumulative

I have a situation where I need to accumulate a dynamic amount through the script. Considering in this example an accumulation of 3 in 3 days.
I need to add the current day, with the next two days, and so on until the last record.

Something similar to using RangeSum (Above(MyField,0,3)). However, I need to do it in the script.

cristianozilz_0-1653403322884.png

 

Labels (1)
4 Replies
brunobertels
Master
Master

Hi 

may be like this 

Load

          RangeSum(Peek(Field), Peek(Field, -2), Peek(Field, -3)) as RangeSUM

         

Here you have to be careful about the load order : use order by Date desc ; 

cristianozilz
Partner - Contributor III
Partner - Contributor III
Author

thank you very much for answering
i got it, but the only problem is that it's not a fixed range value.
There will be products that the interval will be, 3 other 2, another 15, it would have to be dynamic in some way

brunobertels
Master
Master

Hi 

so you can adapt the mesure with a if statement 

if(Product ='product1',

RangeSum(Peek(Field), Peek(Field, -2), Peek(Field, -3)) ,

if(product ='Product2',

RangeSum(Peek(Field), Peek(Field, -2), Peek(Field, -3),Peek(Field, -4),...,Peek(Field, -15))

))

as RangeSUM

cristianozilz
Partner - Contributor III
Partner - Contributor III
Author

Good Morning,
Thank you very much for replying, I was able to assemble the command dynamically. it looked like this:

FOR a=1 to NoOfRows('RangeTab')
LET vLead = Peek('LeadProduto', $(a)-1, 'RangeTab');

SET vCommand = 'RangeSum([qtd]';
For b=1 to ($(vLead)-1)
LET vCommand = '$(vCommand), Peek([qtd], - $(b))';
Next
LET vCommand = '$(vCommand)) as RangeSum';
NEXT