8 Replies Latest reply on Feb 1, 2018 1:40 PM by Deepak Rai

# Calculating Average from two datasources (SQL & Excel)

Good Afternoon!  Here's what I'm trying to do.  I have a connection to SQL server where I obtain number of cases per region.  I have also an excel file where I have data that I want to refer to (i.e. total of ITs in a particular region).

Example below.  The results I expect is to divide the number of cases per number of ITs = 2,501/62 for AFR for example).  Keep in mind that I cannot create a relationship between the two (or at least I don't know how).  I'm attaching a sample based on an Excel workbook but in reality it is a SQL DB that contains the number of tickets, and the excel the information on staffing.  Or should I do something link if this region AFR, then sum all tickets and divide by nr. of ITs, etc.

Any insight will be appreciated. I'm attaching a Tableau Workbook in Version 10.1.

 REGIONAFR # of ITS62 EAP 43 ECA 42 ECR 11 HQ Cluster 22 HUB 1 LCR 28 MNA 17 SAR 15
• ###### 1. Re: Calculating Average from two datasources (SQL & Excel)

You can either Join your datasources or do blending  on some common field and can do calculations. For joining use Cross data base join between SQL DB and EXcel on common field.

Here is It:

For Blending, Use this:

Thanks

Deepak

• ###### 2. Re: Calculating Average from two datasources (SQL & Excel)

Thanks! But my problem is that I do not have a common field between the two sources? What would be a way to do the calculation then?

• ###### 3. Re: Calculating Average from two datasources (SQL & Excel)

If you are after 10.3 Version use Join Calculation to Join using 1 on 1.

Thanks

Deepak

• ###### 4. Re: Calculating Average from two datasources (SQL & Excel)

Thanks but I’m on 10v1 unfortunately so I can’t take a look.  Can we create a calculation?

• ###### 5. Re: Calculating Average from two datasources (SQL & Excel)

In that case you need to add a Column to both your data sources which should have a value of 1 only across all rows . Then you can join...Cumbersome but works...

• ###### 6. Re: Calculating Average from two datasources (SQL & Excel)

Thank you for your time. But this is not an option as I don’t have access to modify the SQL datasource ;-(

Cheers,

Virginie

Virginie D. Bocard

ITSCS – Program, Initiatives & Analytics Team

T +1 (202) 473-9665

vbocard

1818 H Street, NW; Washington, DC, 20433

• ###### 7. Re: Calculating Average from two datasources (SQL & Excel)

Just to clarify. I do not have a common field nor cannot add one to the SQL data source. Would it be possible then to write a formula that would say something like that:

if assigned group = AFR then sum and divide by 62

if assigned group = EAP then sum and divide by 43 etc.  ?

Also would it be possible to graph the results of the calculation?