OK, so this just popped up as a "today" requirement (yaay) and remember, I'm stuck on tableau 9.3 with no option to upgrade. And the data is not of a nature that I am permitted to share my twbx.
I have aggregate data broken out by fiscal year.
Anyways, the customer, not content with a simple YOY calculation as we've been doing in this dashboard for ages, has requested the following formula be applied:
New measure of performance (percentage) = THIS FY's [dollars spent in tiers 1/2/3] divided by LAST FY's [dollars spent overall].
- I have measure objects for the [dollars 1/2/3] and [overall dollars] values already, from the data source
- The FY dimension is NOT a date field, it's a string
- I have built objects for current FY, and to test each FY dimension value for "this FY" and "last FY" (returning T/F)
- I have built separate objects specifically for "this FY dollars 1/2/3" and "last fy overall dollars" by doing IIFs and including the test flags from above
....then I tried to build one to just divide one by the other.
However, because the FYs are their own rows, when I start putting this all into a crosstab to get toward where I need to be, I get the this-fy $ figure in its own row, the last-fy $ figure in its own row, and no value for the division, because the values don't appear in the same row, basically.
What the heck do I do?? It's driving me bonkers.
Well, I was able to sort of force the numbers by using TOTAL in the final calculation, to make it just do the calculation for the whole table and show the same figure across the values for the FY dimension. I'll have to see if I can make a viz play nice with it. *shrug*