Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!
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.
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?
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?