2 Replies Latest reply on Feb 29, 2016 8:05 AM by Samuel Gee

# Using a table calculation with a parameter

Hi there,

I have some products on sale for 8 weeks. They each drop their price at a different week - the "Drop Week" (DW). The amount their prices drop by changes per product. Each product has an average selling price (ASP) each week, as they retail at different prices in different stores.

There is a parameter used as a filter to only show products that dropped their price in a particular week.

In the attached, I'm trying to write a calculated field that returns the % change in the average of the ASP for the same week as that which is specified in the DW parameter.

So one that returned "5.6%", as the example currently stands when you open the workbook, with DW 7 selected. If DW 5 is selected, the field would return "60%".

I'm having difficulty as I don't know how to tell Tableau to find the same row as that indicated by the parameter.

I'm playing with something like "find the AVG(ASP) of the week of sale which equals the drop week, and then divide it by the PREVIOUS VALUE in the table, and then minus the PREVIOUS VALUE" (and then I'll just format to appear as a %).

But the exact syntax I should use escapes me.

Any suggestions and assistance appreciated.

Sam

• ###### 1. Re: Using a table calculation with a parameter

Sam,

Is this what you are looking for?

(LOOKUP(sum([ASP]),[DW Parameter] - ATTR([Weeks on sale]))-LOOKUP(sum([ASP]),[DW Parameter] - ATTR([Weeks on sale])-1)) / LOOKUP(sum([ASP]),[DW Parameter] - ATTR([Weeks on sale])-1)

Artem

• ###### 2. Re: Using a table calculation with a parameter

Hi Artem,

Thank you! It absolutely did.

I was having trouble understanding it until I realised the offset in the LOOKUP applies relatively to each row, not from one fixed point for all rows.

Thanks again! You're the best. Problem solved, and I learned something.