Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

rangesum and the first and last transaction are missing / zero stock analysis

Hello,

Thanks to the post below i'm now able to calculate stock positions on every date in the past.

http://community.qlik.com/forums/t/28859.aspx

This works fine, but i do have one problem using this formula:

rangesum(mutation,if(produkt=Peek('produkt')), Peek('stock'))) AS stock



The oldest and the most recent transaction are not calculated. The transactions in between are calculated the right way.

I tried to catch an isnull situation with an extra if statement at the second peek function, but this doesn't work either.

Does anyone know how to solve this? Or more in general the downside of using rangesum in these situation?

Any help is appreciated!

3 Replies
Miguel_Angel_Baeyens

Hello,

If I get you right, this should work

If(produkt = Peek('produkt'), RangeSum(mutation, Peek('stock') , 1) AS stock


The first record will get "1" as stock, since it has no previous "stock" field loaded, the last record should get the current record mutation(or the field you accumulate).

Hope that helps.

Not applicable
Author

Hi Miguel,

Thank you for your solution. It doesn't work completely yet. But it's good to see that when put the if statement outside the rangesum() function i do get the first transaction. I therefore changed your formula to:

If(produkt = Peek('produkt'), RangeSum(mutation, Peek('stock')) AS stock

when i use your formula the first transaction is also skipped and the result is 1 off.

I still don't get the last transaction to work though, i tried to change the 1 into 0 but this also doesn't work. Perhaps it's just not possible with the formula used?


					
				
			
			
				
			
			
			
			
			
			
			
		
Miguel_Angel_Baeyens

Hello,

In case of the last record, which would be the calculation? My guess is that "stock" plus "mutation", so the "else" part of the if() function should return the value for the first "produkt" (when produck ist not equal to Peek(''produkt')). If the value in "produkt" exists, then the RangeSum() function should work.

What am I missing?