4 Replies Latest reply on Jun 28, 2018 4:50 PM by Peter Fakan

# How to automate my calculation formula by indexing to avoid hard coding every quarter

HI all,

I need to extract data for "Current Quarter -3" , "Current Quarter -2" , "Current Quarter -1" , "Current Quarter".

Current quarter now is 2018 Q2, so my formula for a quarter up to date is:

IF  YEAR([Fiscal Quarter])= 2018

AND DATEPART('quarter',[Fiscal Quarter]) <=YEAR(TODAY())

THEN [Ship + Cust Open Orders CRD]

END

This formula has automated my calculation. I don't need to hard code in year = 2018 and quarter = 2 and update my code every new quarter.

But I don't know how to do this for other quarters:

for example, for "current quarter - 3" , I have hard coded the formula has below: (for 2017 Q3):

ZN(IF str(DATEPART('year',[Fiscal Quarter] ))= '2017' AND str(DATEPART('quarter',[Fiscal Quarter] )) ='3'

THEN [Ship Final Act-Fcst]

END)

I want to update that above formula to the automated calculations like my Quarter to date formula. So that I don't need to manually input Year and Quarter every time.

I cannot share my data but I have provided some sample data.

I really look for any advice on the code. I don't want to hand change my year and quarter every new quarter, thank you so much!

• ###### 1. Re: How to automate my calculation formula by indexing to avoid hard coding every quarter

Hi Helen

Does this help?

Don't try to create many measure values, but you can modify header.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: How to automate my calculation formula by indexing to avoid hard coding every quarter

Hi Helen,

I took a different approach to Shin, please find my version where I have replaced the quarters and years with parameters so you don't have to manually input the fields all the time, just change the parameter.

HTH 18.1 attached

Peter

1 of 1 people found this helpful
• ###### 3. Re: How to automate my calculation formula by indexing to avoid hard coding every quarter

Thank you so much peter. I have two questions:

1. Could you please let me know why we use Parameter here? What is the benefit?

2. Could you please share the .twbx for Tableau 10.3? I cannot open your workbook

3. Could you please help me take a look at this question as well? How to automate the calculation for quarter over quarter ratio

Thanks

1 of 1 people found this helpful
• ###### 4. Re: How to automate my calculation formula by indexing to avoid hard coding every quarter

Hi Helen,

The parameter allows me to change the value inside the calculated field, the benefit is its kind of like a faster way of filtering.

Attached is the 10.3 workbook

HTH

Peter