4 Replies Latest reply on Oct 6, 2016 8:53 AM by Samuel Gee

# Turn date dimension into relative references

That title is probably worded terribly, I'm sorry.

We have tons of products, as in the attached workbook, which are released on different dates into the market. So a list of x products will have - as in the view - lots of empty space in any viz where there's a date dimension in the columns shelf.

What I very frequently have to do is compare or average out the Week 1 of sales for all products, or the week 8. Or build a decay curve of sales for the first 52 weeks, or so on.

Currently I export from Tableau to Excel, delete the empty cells in the table and shift all the data to the left. That means the first column of sales after the title name is Week 1, the second column is every product's Week 2, etc.

Is there a way of achieving this in Tableau? With the first week of sales for each product in the first column of the view, regardless of when that product was released?

All thoughts appreciated! There's also a release date field in the actual data but not the attached file, where the release date for each title == the W/C value for the first week of sales for that title. If that would be useful for any solution I can add it into the twbx.

Thanks very much,

Sam

NB: Second request: it would be enormously helpful if this could be achieved with calculated fields and not table calculations, as ideally I'd be able to do the same for product categories and sub-categories without needing to have the (thousands and thousands) of products in the view.

• ###### 1. Re: Turn date dimension into relative references

I feel like you're going to have to do table calculations for this, but I don't see how that would require you to have all your product categories and sub-categories in the same view (I guess it's tough to tell from the sample).

Here's how a table calc will achieve it;

> Drag Industry Week onto the detail button on the marks card

> Create a calculated field with the formula INDEX() or add the formula directly onto the columns shelf.

> Edit the table calculation and set it to compute using specific dimensions. Select Title and Industry Week Value and make sure they are in that order. 'At the level' = deepest and restarting every = 'Title'

• ###### 2. Re: Turn date dimension into relative references

Thanks Tom. That gets me a slightly garbled viz, but I'll keep poking it to find out how I'm mucking it up.

Is there a way of getting the table headings as well? Week 1, Week 8, Week 52? We frequently report like this and it would be handy to be able to dashboard it.

• ###### 3. Re: Turn date dimension into relative references

Make sure you set the pill to be discrete, not continuous.

You could create a calculated field like "Week " + STR(INDEX()) and set it up the same way I described above

• ###### 4. Re: Turn date dimension into relative references

Thank you Tom