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

# Sum Reserve 6 month rolling calculation

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.

• ###### 1. Re: Sum Reserve 6 month rolling calculation

Hi Sandra,

Please post sample data along with the expected result.

~Tushar

• ###### 2. Re: Sum Reserve 6 month rolling calculation

Hi Tushar,

Please see below some sample data:

 id time amount status In/Out first_name last_name Curr Vendor 84403236 12/01/2016 500 C In A B USD V 84404208 12/01/2016 500 P In C D USD P 84405921 12/01/2016 500 P In A A USD P 84406623 12/01/2016 500 P In C C USD P 84406756 12/01/2016 800 P In J B USD P 84407429 12/01/2016 -50 C Out G D USD V 84409545 12/01/2016 -500 P Out K A USD P 84409552 12/01/2016 -1855 P Out L C USD P 86791038 12/02/2016 500 C In A B USD V 86792383 12/02/2016 5000 P In C D USD P 86793423 12/02/2016 200 C In A A USD V 86794809 12/02/2016 1000 P In C C USD P 86796064 12/02/2016 500 P In J B USD P 86796007 12/02/2016 1100 P In G D USD P 86798239 12/02/2016 -2000 P Out K A USD P 89147361 12/03/2016 600 P In K L USD P 89147983 12/03/2016 5600 P In R D USD P 89148693 12/03/2016 180 C In O D USD V 89154019 12/03/2016 100 C In S A USD V 89158648 03/04/2017 1000 P In S S USD P 89159217 04/30/2017 -2000 P Out L L USD P

The Total is \$12,175. The reserve is 20 % --> \$2,435 as of 1st of May 2017.

On 2nd May 2017 we have new transactions:

 86793423 05/03/2017 175 P In L L USD P 84409545 05/03/2017 -500 P Out K A USD P

Total will be \$11,455 and Reserve \$2,291.

I want to create a weekly chart to represent the Reserves and Net Amount

• ###### 3. Re: Sum Reserve 6 month rolling calculation

Hi Sandra,

I can't get my head around it. I would like to ask Tableau ambassador and zen master Shinichiro Murakami

Sorry to ping you like that. Could you please assist in this.

~Tushar

• ###### 4. Re: Sum Reserve 6 month rolling calculation

This should give you rolling 6 months value.

If you multiple it by 0.2, you get closer to your result.

~Tushar

1 of 1 people found this helpful
• ###### 5. Re: Sum Reserve 6 month rolling calculation

Thank you for ping me.

HI Sandra,

I am not sure your definition of 6 month.

For example today is 2018/4/18, 6 month ago means

A. 2017/10/18  or  B. 2017/10/1

Your sample calculation is little bit off and I am confused.

In my example, I used case B

Anyway hope this helps.

Prepare another calendar table to join to eliminate missing date.

Thanks,

Shin

3 of 3 people found this helpful
• ###### 6. Re: Sum Reserve 6 month rolling calculation

Thank you Shin,

This helps me.

Regards,

Sandra

• ###### 7. Re: Sum Reserve 6 month rolling calculation

HI Sandra,

You are welcome.

Shin