If I understand this correctly, you want to select a quarter, which will then act as the maximum quarter shown, then count each quarter backward. If that's the case, then I'd suggest that you use a parameter for the maximum quarter (I've called my parameter FY Quarter). You can then do a split on FY Qtr to create two new calculated fields, one which will give you the year number and one which will give you the quarter number. You'll need to do something similar based on the parameter to get the max year and quarter numbers. Here are those calculated fields:
FY Qtr - Quarter Number
INT( SPLIT( SPLIT( [FY Qtr], "FY", 2 ), "Q", 2 ) )
FY Qtr - Year Number
INT( SPLIT( SPLIT( [FY Qtr], "FY", 2 ), "Q", 1 ) )
Max Quarter Number
INT( SPLIT( SPLIT( [FY Quarter], "FY", 2 ), "Q", 2 ) )
Max Year Number
INT( SPLIT( SPLIT( [FY Quarter], "FY", 2 ), "Q", 1 ) )
You'll also need a calculated field to filter out values greater than the max you've selected.
IF [FY Qtr]> [FY Quarter] THEN
Drag Include to filters and select only the "Include" value.
Finally, you can create a calculated field that does the math to create the counter.
(([Max Year Number]-[FY Qtr - Year Number])-1)*4 + 4+([Max Quarter Number]-[FY Qtr - Quarter Number])
See attached workbook. If this resolves your question, please be sure to mark this as the "correct answer." This will close the thread and help others who may have similar questions in the future. Thanks!
Assign FY Qtr's number.twbx 23.8 KB
Thanks for response. i still use 10.4 version of Tableau desktop, hence was unable to open your file. Also i see Split function is not available in 10.4 version.
My FY Qtr list gets automatically updated, hence i will not be able to use parameter there. I can take ATTR([FY Qtr]) to get max if qtr selected.
Could you suggest any alternative.
Here is a version in 10.4 format. Split is definitely available in 10.4, so that should work.
I'd have to think about an alternative which allows you to use a filter instead of a parameter. That makes it significantly more difficult. Since there are only 4 quarters in each year, I'd probably suggest just pre-populating the parameter before a new quarter starts--perhaps you could do this once per year.
Hi Shruthi, any further questions on this?
Thanks so much. Your help is much appreciated.