I have two tables:
Category | Name | Goal | WK40 | WK41 | WK42 | WK43 | WK44 | WK 45
Data | Data | Data | Data | Data | Data | Data | Data | Data
W1 | W2 | W3 | W4 | W5 | W6
WK40 | WK41 | WK42 | WK43 | WK44 | WK45
In Table 1, the WK40, WK41 etc. column names are dynamic (i.e., these column names change every week depending on the current week of the year & then shows the last 6 weeks). Table 1 has all the required data.
In Table 2, the column names are W1, W2, ...W6 and the column name here is static. But the data in each column (WK40, WK41....) changes every week based on the current week.
I want to know how I can use these two tables in Tableau to build calculations like Week over Week calculation : (W6 - W5 ) /W6 & this should reference data from Table 1 (i.e., refer WK45- WK44)/WK45 in this case).
If I Union the two tables, then how to use the W6 column to get the value from WK45 column? Like doing (W6 - W5 ) /W6 should take the value of W6, W5 which is WK45, WK44 and essentially do (WK45 - WK44)/WK45
You may not be able to write queries on a table where the structure is not permanent. So, I would suggest that you structure your data in a pivoted format, outside of Tableau using SQL and/or some scripting language and write to a table or a file, and then let Tableau read from that table/file.