Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
YTD-questions seems to be a common question but I can't find a previous solution for my YTD-problem.
I got a table that inclueds Unit, Period and Amount_Month. I like to calculate a new field in the script with Amount_YTD as shown in the table below.
Unit | Period | Amount_Month | Amount_YTD |
C | 200910 | 25 | 25 |
C | 200911 | 50 | 75 |
C | 200912 | -600 | -525 |
A | 201001 | 400 | 400 |
A | 201002 | -200 | 200 |
A | 201003 | 0 | 200 |
A | 201004 | 300 | 500 |
A | 201005 | -500 | 0 |
A | 201006 | -100 | -100 |
A | 201007 | 200 | 100 |
A | 201008 | 300 | 400 |
A | 201009 | 0 | 400 |
A | 201010 | 100 | 500 |
A | 201011 | 250 | 750 |
A | 201012 | -150 | 600 |
B | 201001 | 200 | 200 |
B | 201002 | 300 | 500 |
B | 201003 | 0 | 500 |
B | 201004 | 100 | 600 |
B | 201005 | 250 | 850 |
B | 201006 | -150 | 700 |
B | 201007 | 400 | 1100 |
B | 201008 | -200 | 900 |
B | 201009 | 0 | 900 |
B | 201010 | 200 | 1100 |
B | 201011 | 300 | 1400 |
B | 201012 | -150 | 1250 |
Any ideas?
Hello Kristofer,
Using peek() and previous() functions will help you to do that. My untested guess for your new field is
If(Unit = Previous(Unit), Rangesum(Amount_Month, Peek(Amount_Month))) AS Amount_YTD,
Of course you can add new conditionals to compare with previous periods or previous fields.
Anyway, the more complex your field is, the longer the load will take.
Hope that helps!
Hello Kristofer,
Using peek() and previous() functions will help you to do that. My untested guess for your new field is
If(Unit = Previous(Unit), Rangesum(Amount_Month, Peek(Amount_Month))) AS Amount_YTD,
Of course you can add new conditionals to compare with previous periods or previous fields.
Anyway, the more complex your field is, the longer the load will take.
Hope that helps!
Thanx for your answer. Your tip got me on the right track. I had to adjust the last peek to Amount_YTD. This soleved my problem:
If
(Unit = Previous(Unit) and left(Period,4) = previous(left(Period,4)), rangesum(Amount_Month, Peek('Amount_YTD')),Amount_Month) as Amount_YTD