7 Replies Latest reply on Jan 14, 2017 2:05 AM by Norbert Maijoor

# How to get Top 5 %  values and scorecard while using data blending

Hello All,

I am reposting this question as I didn't receive any solution for this and this is my priority now.

I am using Tableau 10.0.1 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

Priya

• ###### 1. Re: How to get Top 5 %  values and scorecard while using data blending

Hey Priya,

It's not the idea of the Tableau Community to post your question again and again day after day.

You will not get a faster response by just adding your question multiple times.

Regards,

Matthias

• ###### 2. Re: How to get Top 5 %  values and scorecard while using data blending

Priya,

Upfront for your info. There is only for the month december 2012 in both sources data. Before you would like to continue you would like two sources with multiple month stored.

• ###### 3. Re: How to get Top 5 %  values and scorecard while using data blending

Priya,

Now that I am looking at your questions.

1) Why are you working with blending? Have you tried using cross-database joining? This would make your requirement much easier.

Cheers,

Matthias

• ###### 4. Re: How to get Top 5 %  values and scorecard while using data blending

Hi Priya,

Find my approach based on one data source (Superstore..and did some renaming) as reference below and stored in attached workbook version 10.1

• ###### 5. Re: How to get Top 5 %  values and scorecard while using data blending

Hello Maijoor,

Thank you for the reply, but you didn't use measure value from second data source, so blending concept is not applied.

But in my workbook as you can see AP Spend is coming from one data source and SF Spend is coming from secondary data source.

You did sorting to Month of Order Date by right clicking field-sort-Field-spend%. But in my case I cannot select Spend% because that field does not appear in my sort field function.

(AP Spend/SF Spend*100) in my Sort- Field function.

In the below picture in the Field, I don't see Spend% in the list. So I was unable to sort by Spend%

Kindly look my attached workbook once at Sheet6.

My final goal is to sort by Spend% and get top 5 and bottom 5 months.

Thanks,

Priya

• ###### 6. Re: How to get Top 5 %  values and scorecard while using data blending

Hello Matthias,

I did try cross data joins and I am getting error like "unable to materialize temporary table", fields I am using to join both the data sources are of same data types. I am not sure why I am receiving this error.

So I started using data blending here and for some reason I am unable to sort a calculated field which contains data from both primary and secondary data source.

Thanks,

Priya

• ###### 7. Re: How to get Top 5 %  values and scorecard while using data blending

H Priya,

As mentioned in my earlier post only the month december for the years 2000-2012 available in the World indicators datasource.

In the Superstore datasource only the the years 2012-2015 are available. So you could only "blend" on one month (december 2012)