1 2 Previous Next 15 Replies Latest reply on Oct 3, 2018 6:07 AM by Deepak Rai

# Trying to make calculated column with the value of another column 30 days prior

Tried a few things here and seem to be struggling. What I am trying to do is compare actual revenue for a day vs projected revenue from 30 days prior. Tried the calculated field below but as you can see that just pulls the forecast revenue for today -30. What I'd like to do is populate the projected revenue column with the "Forecast Revenue" from 30 days before that date.

I messed around with a date -30 field but still couldn't really wrap my head around it.

Thanks for the help!

• ###### 1. Re: Trying to make calculated column with the value of another column 30 days prior

Can you explain a bit..What is ask here?If you have some fake workbook it will help u faster.

• ###### 2. Re: Trying to make calculated column with the value of another column 30 days prior

So I've been asked to calculate Realized Revenue %. Forecast Revenue is a 30 day projection of revenue and Actual Revenue is the realized revenue from the last 30 days. What I want to be able to calculate is Realized Revenue % (Actual Revenue / Forecast Revenue from 30 days prior).

Hope that makes more sense. I can try and explain it another way if it's still not clear.

Thanks,

• ###### 3. Re: Trying to make calculated column with the value of another column 30 days prior

If I understood Correctly, Then You need Forecasted data 30 days back, so for that , you may do forecasting for last month using data from your minimum date to the 2nd last month and use that forecasted value in your Calculation.

• ###### 4. Re: Trying to make calculated column with the value of another column 30 days prior

So I have data going back multiple months. The core of the question is how to write a calculated field that compares data from one row to another column on a row 30 days prior. So for example: = Actual Revenue (10/1) / Forecast Revenue (9/2)

• ###### 5. Re: Trying to make calculated column with the value of another column 30 days prior

If you need that Calculation, Please post some fake data and I can try to get you there.

• ###### 6. Re: Trying to make calculated column with the value of another column 30 days prior

Here's some data with faked revenue numbers. A few caveats:

• I realize at this point there are only going to be a few rows in which we can look back 30 days but this table will be updating daily going forward.
• There are a few rows missing from the data where we experienced an outage with our ETL server. I understand that there will be some nulls in the future due to this.

Again thanks so much for your help. I'm already starting to see how powerful Tableau can be but it is also quite confusing while I get used to the platform.

• ###### 7. Re: Trying to make calculated column with the value of another column 30 days prior

I could not find any data attached.

• ###### 8. Re: Trying to make calculated column with the value of another column 30 days prior

Sorry about that. I've updated my response.

• ###### 9. Re: Trying to make calculated column with the value of another column 30 days prior

So from your example, would you explain what do you want to divide by what?

• ###### 10. Re: Trying to make calculated column with the value of another column 30 days prior

I've added an additional column with the excel formula I am trying to replicate.

The goal is to more closely follow our forecasting accuracy.

• ###### 11. Re: Trying to make calculated column with the value of another column 30 days prior

There is no formula

• ###### 12. Re: Trying to make calculated column with the value of another column 30 days prior

Not sure why but here's a screenshot:

• ###### 13. Re: Trying to make calculated column with the value of another column 30 days prior

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

1 of 1 people found this helpful
• ###### 14. Re: Trying to make calculated column with the value of another column 30 days prior

Yeah that should work. I was trying to think of it as a date function but I don't really see any harm in just using a lookup so long as we don't have gaps in data.

Thanks for the help!

1 2 Previous Next