Date calculation - Tableau

0
votes

We have data that is submitted that is only YTD numbers. I'm wondering how I could display numbers that are subtracted along the Date field. Ie, if I want to show the MTD movement on March. I will have to go March less February.

Now I know I can do this for individual measure fields. But having around 40+ measures seems a bit tedious. http://kb.tableausoftware.com/articles/knowledgebase/creating-ytd-mtd-calculations

I tried to enter "Measure Values" but that is not a valid measure to put in the calculation. Is there a way to set up a custom dimension?

Thanks, Gem

1
tableau-api
Can't think of any efficient way to do this for 40 measures at once. All I can think is doing some table calculations, but you would have to do it for every measure, which is not what you want. Bear in mind that Tableau is a data visualization tool, and though it can be used for some data manipulation, most of it should be done outside Tableau, and connect only ready databases – Inox
I thought so, I had previously used the sql pivot, to restructure my table. Any suggestions on how I can do a calc in SQL, this will need a parameter to be passed. It's tricky, they are asking for a rolling twelve months, and the data presented is only YTD, so I can't even use a simple sum(*) the last 12 months. I have to do March14 + Dec13 - March13... so on to get a rolling twelve month. – gemmo
Open a new question with SQL tag, someone that knows SQL better than me will be glad to help. Thinking of the logic, if you have daily entries with YTD values, all you need is to subtract each record from the one before it to get the actual value for that day (except for 1/1), right? Than you can plug in to Tableau and work with it properly. Again, it can be done in Tableau, but it involves creating 40+ calculated fields. I'm sure there's a better solution in SQL – Inox

1 Answers

1
votes

After days of research, can't be done in tableau unless you want to labour for a week creating an almost cell by cell calculation. Data transformation in SQL will be a more feasible solution.

I had pivoted the data previously in SQL, so that I end up with 1 measure column instead of 40+. That enables you to minimise the calculation fields, so that you don't have to repeat all the calculation for individual measures.

Works well. Not for ratios though, as you will need to extract individual measures again so that you can divide them against each other. It's got pros and cons. Number of rows in the DB also multiplies.

Other solutions that preserves the table structure will be to use temp tables and do calculations on several temp tables.