10 Replies Latest reply on Jul 6, 2018 1:21 PM by Helen Wang

# How to automate the calculation for quarter over quarter ratio

Hello everyone,

I am working on a Tableau Dashboard. My current dashboard requires a calculation, which is listed below:

(Current quarter on quarter management DA):

QoQ Cur Q MGT DA

[Q2'18 MGT DA ]/[Q1'18 MGT DA ]-1

Below is the formula of how I get :[Q2'18 MGT DA ]

Q2'18 MGT DA

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

THEN [Ship Final Act-Fcst]

END)

Below is the formula of how I get :[Q1'18 MGT DA ]

Q1'18 MGT DA

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

THEN [Ship Final Act-Fcst]

END)

: I want to automate the current quarter to quarter calculation.

It will be just how to index the current quarter [Ship Final Act-Fcst] and the previous quarter [Ship Final Act-Fcst]

It would be great if anyone may give me advice on how to write a code like that. Thank you!

• ###### 1. Re: How to automate the calculation for quarter over quarter ratio

Q2'18 MGT DA

ZN(IF str(DATEPART('year',[Fiscal Quarter]))= '2018' AND str(DATEPART('quarter',[Fiscal Quarter] )) =STR({MAX(DATEPART('quarter',[Fiscal Quarter] )})

THEN [Ship Final Act-Fcst]

END)

Q1'18 MGT DA

ZN(IF str(DATEPART('year',[Fiscal Quarter]))= '2018' AND str(DATEPART('quarter',[Fiscal Quarter] )) =STR({MAX(DATEPART('quarter',[Fiscal Quarter] )}-1)

THEN [Ship Final Act-Fcst]

END

• ###### 2. Re: How to automate the calculation for quarter over quarter ratio

HI Deepak,

I cannot figure out what parenthesis is missing.....could you please let me know?

ZN(IF str(DATEPART('year',[Fiscal Quarter]))= '2018' AND str(DATEPART('quarter',[Fiscal Quarter] )) =STR({MAX(DATEPART('quarter',[Fiscal Quarter] )}-1)

THEN [Ship Final Act-Fcst]

END

1 of 1 people found this helpful
• ###### 3. Re: How to automate the calculation for quarter over quarter ratio

Q1'18 MGT DA

ZN(IF str(DATEPART('year',[Fiscal Quarter]))= '2018' AND str(DATEPART('quarter',[Fiscal Quarter] )) =STR({MAX(DATEPART('quarter',[Fiscal Quarter] ))}-1)

THEN [Ship Final Act-Fcst]

END

Try now

1 of 1 people found this helpful
• ###### 4. Re: How to automate the calculation for quarter over quarter ratio

Dear Deepak,

Thank you for the formula. It worked. But is there a reason why the result is larger?

Q1' 18 Gross Ship 2 is  your formula, while Q1'18 Gross Ship is my original formula.  I expect the result to be the same

• ###### 5. Re: How to automate the calculation for quarter over quarter ratio

I used FIXED, If you have some filters, add Them to CONTEXT to get same Results

• ###### 6. Re: How to automate the calculation for quarter over quarter ratio

Hi Helen,

Just responding from the other post, if you want to automate the calculations, using Deepak's response replace the '2018' with a parameter, and the Quarter selection with a current quarter parameter (1-4).

You can work out the previous quarter for your calculated fields using the same parameter that you use for current quarter with another calculated field (pseudocode below) or just add another parameter to let users set the previous quarter as well.

If [current quarter] = "1" then [last quarter] = "4" AND [Year] = [Year Parameter]-1

elseif [current quarter] = "2" then [last quarter] = "1"

elseif [current quarter] = "3" then [last quarter] = "2"

elseif [current quarter] = "4" then [last quarter] = "3"

END

HTH

Peter

• ###### 7. Re: How to automate the calculation for quarter over quarter ratio

https://community.tableau.com/message/787842#787842

Peter would you mind helping me with this question?

Have a nice weekend!

• ###### 8. Re: How to automate the calculation for quarter over quarter ratio

https://community.tableau.com/message/787842#787842

Peter would you mind helping me with this question?

Have a nice weekend!

• ###### 9. Re: How to automate the calculation for quarter over quarter ratio

https://community.tableau.com/message/787842#787842

Peter would you mind helping me with this question?

Have a nice weekend!

• ###### 10. Re: How to automate the calculation for quarter over quarter ratio

https://community.tableau.com/message/787842#787842

Deepak would you mind helping me with this question?

Have a nice weekend!