2 Replies Latest reply on Feb 19, 2013 2:28 PM by Susan Oner

# Calculated field question

Hi Tableau forum,

I'm attempting to look at store "returns" charted over the period after purchase, and I'm having a problem getting the average calculation for the return rate.  In this case, a return is defined as within 30 days; the rate is simply returns within 30 days divided by total number of sales.

I'd like a viz that shows the day of return on the x-axis, and the return rate for each day on the y-axis.  Using the forums for help, I've gotten a viz (please see attached) that almost gets it, but doesn't include the total number of sales in the calculation - that is, it only compares each day's returns to each day's sales according to the report date.  Instead I'd like to compare returns to an average of sales by day (same denominator for each of 30 days), but I can't seem to write the calculated field.

There is an example workbook attached, with data from one store over two years.  In this period, there were 139 returns over 626 purchases, with a return rate of 22.2%.  626 divided by 30 days is ~21, which I'd like to use as the denominator for calculating return rate by each day.  So for example there were 6 returns after 1 day of purchase, so 6 div by 21 would show a Day 1 return rate of ~29%, and so on.  Ideally, on the graphy the average reference line would equal 22.2%.  How do I get this calculation in Tableau?

Thank you, any help would be really appreciated!

Susan

• ###### 1. Re: Calculated field question

Hi Susan,

See the attached. First of all, since the Days to Return (copy) is in the view, any SUM(Returns) is going to be partitioned by just the Days to Return because the view is at that level of detail. In that case, to generate a total of all returns, we turn to table calculations, for this worksheet the TOTAL() calc works - you can see that in the workout worksheet.

Secondly, the total number of sales crosses more than 30 days, and you were running into an issue with the order of operations in Tableau. In the "table - all" worksheet there are 626 purchases, but in the "graph - rate each day" worksheet you had filtered only for returns from 1-30 days, so there were fewer records to be computed over. The trick here is to use a table calculation filter, which is applied after most other calculations are computed (but before most reference lines are computed). I created a calc with the formula LOOKUP(ATTR([Days to Return]),0) and used that instead of the other filter, and now the reference line shows 22.2%.

Jonathan

• ###### 2. Re: Calculated field question

Thank you, Jonathan!  This is an elegant solution.

I think I need to brush up on (actually- learn) table calculations.  Thank you again

SO