7 Replies Latest reply on Apr 19, 2018 8:49 AM by Shinichiro Murakami

    Sum Reserve 6 month rolling calculation

    Sandra M

      In the raw data, I have Amount (USD), Vendor and Time.

       

      I would like to create a calculation for the reserves based on 6th months - Reserve = 0.20 * SUM([Amount USD] in last 6 months)

       

      Let’s say for example w received a $100 every day and give back $50 every other since January 1. So on June 1st, the net amount within the last 6 months is $11,400. Thus the reserve as of June 1st would be $2,280. Then on June 2nd, the net amount within the last 6 months is $11,450. Thus the reserve as of June 2nd would be $2,290.

       

      I created Six months field - DATEADD('day',-180,[time])

      and Reserve field - IF DATETRUNC('month',[time]) >= [6 month ago] THEN [Amount (USD)] END

       

      I do not think I get the right numbers.

       

      Please advise