2 Replies Latest reply on Nov 9, 2018 7:20 PM by Fariz Anwardeen

# Get Column Percentage for Calculated Field

I have a Calculated Field calculated called Buy/Sell Totals which is calculated as

IF ATTR([Application Strategy Buy/Sell]) = 'Sell' THEN  [Calc Sell Total]  ELSE WINDOW_SUM( COUNT([Application Code])) END

I want to get the percentage for each Quarter calculated as follows

For example the first Quarter FY1900 Q1 for Buy should be calculated as  1339 / (1339+ 640) * 100

Sell should be calculated as  640 /  (1339+ 640) * 100

the second Quarter FY2018 Q4 for Buy should be calculated as  1339 / (1339+ 589) * 100

Sell should be calculated as  589 /  (1339+ 589) * 100

I tried creating a formulas in a Calculated as follows it worked only for the Sell row giving percentage of 30.55, 28.36 etc.

IF ATTR([Application Strategy Buy/Sell]) = 'Sell' THEN

[Calc Sell Total] / ([Calc Sell Total] + [Buy Total]) * 100

ELSE

[Buy Total] / ([Calc Sell Total] + [Buy Total]) * 100

END

The reason for this is i think is because we have data for Buy only in FY1900 Q1

and data for Sell only for all quarters except FY1900 Q1.

Could you please help in getting the below results as percentages of columns ???. Unfortunately I will not be able to provide the tableau workbook since the data is confidential.

Thanks in advance for the help.

• ###### 1. Re: Get Column Percentage for Calculated Field

Fariz,

It's hard to tell without seeing how your data is structured.

Could you post some fake data in a format similar to your real one?

I think it may be easier to manage if you use Level of Detail calculations.

IF ATTR([Application Strategy Buy/Sell]) = 'Sell' THEN  [Calc Sell Total]  ELSE WINDOW_SUM( COUNT([Application Code])) END

would become something like:

IF [AppStratBuy/Sell] ='Sell' THEN [CalcSellTotal] ELSE {FIXED [AppStrategy]:COUNTD(AppCode)} END

[CalcSellTotal] may also need to be converted to an LOD.

• ###### 2. Re: Get Column Percentage for Calculated Field

Thank You Swaroop. I was able to achieve what I wanted using the LOOKUP function is tableau. Really Appreciate you help.