1 Reply Latest reply on Jan 14, 2017 2:26 PM by Shinichiro Murakami

Sort by Percentage when using blending concept

Hello All,

I am using Tableau 10 version and In my attached workbook you can see AP Spend and SF Spend from 2 diff data sources and I am using blending here.

My goal is I am doing comparison for AP and SF Spend from Jan to Dec and also to find  what % of SF Spend is AP, in order to achieve this I did Calculation as

Spend% = sum(SF spend)/ sum(AP spend)*100.

1. Now I want to get top 5 months by Spend%?

Example: I want top 5 months with highest percentage in one sheet and also bottom 5 months with lowest percentage in one sheet with same structure like bar chart having both AP Spend and SF Spend and also Spend%.

and

2. I also wants to show a scorecard which shows the below function in another sheet

Example: by creating KPI Parameter as High, Medium, Low and High should show months with specific percentage like

Spend%>15% then High

Spend%<15% and Spend%>5% then Medium

Spend%<5% then Low

Now if we select High in parameter then it should show specific months which has high percentage i.e. >15% , if we select Medium in parameter then it should the months whose percentage is Spend%<15% and Spend%>5% ......likewise Low.

I tried this parameter function by creating a parameter first as High, Medium, Low as String and created a calculated field as

Calculation for Scorecard:

case [Parameter]

when "High" then Spend%>15%

when "Medium" then Spend%<15% and Spend%>5%

when "Low" then Spend%>15%

end

but I am getting calculation error. and not sure how to achieve both 1 and 2.

Please find the attached sample workbook, in my real time I synchronized the axis and in sample i was unable to do that..fyi

Thanks,

Priya

• 1. Re: Sort by Percentage when using blending concept

Hi Priya

I hope I understand your question correctly.

// Only filter "show"

[Filter Rank]

if Rank([spend%],'asc')<=5

or Rank([spend%],'desc')<=5

then "show" else "hide" end

// Only filter "True"

[Scorecard Param Filter]

if [Scorecard Parameter] = "ALL" then True

elseif [Scorecard Parameter] = "High" then

[spend%]>=15

elseif

[Scorecard Parameter] = "Medium" then

[spend%]>=5 and

[spend%]<15

elseif

[Scorecard Parameter] = "Low" then

([spend%])<5

end

Thanks

Shin

1 of 1 people found this helpful