5 Replies Latest reply on Jul 30, 2018 2:00 AM by Shruthi B S

# Assign Number to FY Qtr's based on filter selection

Hi People,

I have query on assigning numbers to Qtrs absed on the max Qtr selected. Workbook for reference attached nd scenario is explained below.

In a single value drop down filter (FY Qtr) if FY19Q1 is selected

, then i want to assign FY19Q1=0

FY18Q4=1

FY18Q3=2 and so on through a calculated field.

This should be dynamic on any other qtr selection. How can i achieve this.

I want to pick previous qtr , prev-1 qtr based on the selection of FY Qtr, hence require this.

Thanks in advance for all the people who take time and interest in resolving the queries posted.

Regards,

Shruthi

• ###### 1. Re: Assign Number to FY Qtr's based on filter selection

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.

Include

IF [FY Qtr]> [FY Quarter] THEN

"Exclude"

ELSE

"Include"

END

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.

Quarter ID

(([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!

• ###### 2. Re: Assign Number to FY Qtr's based on filter selection

Hi Ken,

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.

-Shruthi

• ###### 3. Re: Assign Number to FY Qtr's based on filter selection

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.

• ###### 4. Re: Assign Number to FY Qtr's based on filter selection

Hi Shruthi, any further questions on this?

• ###### 5. Re: Assign Number to FY Qtr's based on filter selection

Thanks so much. Your help is much appreciated.