2 Replies Latest reply on Apr 22, 2016 8:28 AM by Susan Baier

# Charting Surveys:  How to weight by city factor?   HELP!

@

Hello, im very new to tableau, trying to do some things to make a client use it to replace another software.

Right now, Its start to get complex... as he told me that It would need to do "weighted" calculations of the survey data aquired.

I have done some of the calculations needed, but I really dont know how to implement this on all charts.

Let see... I have
1) Some Questions, done in some Cities
2) Each city have a value, used to calculate a "Poblation %" of the total.
3) City FACTOR, wich is another calculation I will explain.

and what I need is all charts in percentage, but weighted.

1) Simple weighted charts
2) Combined charts (shows value or calculations of some questions, in one graph).

Basically... I have all the graphs done, I can do all of them ... but Unweighted.

City FACTOR... is the key thing...  I found to be able to calculate it using this formula :
SUM([Number of Records])/SUM({INCLUDE [City] : COUNTD([ID])})*ATTR([Poblacion %])

Not sure if its done ok... on the "Data Source" it shows as "Undefined" because something of aggregate function?...

Basically.... the City FACTOR should be calculated like on this table:

We calculate Total of Population for the cities (in red)
We calculate Total Surveys done on each city (in red)

Then, we calculate the % of population on this sample like Total_Population / City_Population ... = 0.xxx
And the sum of all Population %, should be "1" (or 100% in green).

Now... the City Factor!....

This is calculated like this :

Total_Surveys / City_Surveys * City_Population_% =  xxxx
For the first line "caba", the result comes from:  171 / 30 * 0.1289 = 0.73473

Well, in excel looks very easy.... but on tableau, im lost.

Problem is that this value depends on many factors, for example, we need to "count" how many surveys we have total, as well as how many surveys we have for each city.

So, the factor will vary, for each new survey done... basically... making for example, 10 more surveys on "caba", will affect ALL City Factor values, as its based on total surveys done as well.

The Population value, is one I have basically put by hand in a calculated field, so it show on a extra column the poblation percent we can use to calculate for this result.

Now... to the results.... table looks like this

IDCityQuestion 1Question 2Question 3Question 4*Calculated Population %*
1CabaPOSITIVAPOSITIVAENTRE POSITIVA Y REGULARNEGATIVA0.1288
2TucumanPOSITIVANEGATIVAPOSITIVAENTRE NEGATIVA Y REGULAR0.0567

So... what I do, is make a graph, counting positive, negative, etc. results, to make a graph like this ... the problem is that the graph is NOT weighted. So each "POSITIVE" will count as ONE.
And for example.... if Caba weight is 0.1288, and Tucuman weight is 0.0567 ... each "caba" should count more than double of each "tucuman" one, as it has more than double weight.

There is also, another chart, where I sum positive + entre positive y regular, to present a summary for all questions in one graph, using calculated fields (you can see it on the attached worksheet)

So... question is ... HOW DO I EXACTLY ... do all graphs weighted ?   (one graph for each question, plus some other graphs using calculated fields with summaries)

I Attach the Tableau File.

Resuming....making it simple to understand the logic.

Say I have one question, answer is YES or NO, and Also the City,  I make 25 surveys, 10, 10 and 5.
CityOne:   2 x YES .... 8 x NO ......% Population = 50%   --- > Factor = 25 (total surveys) / 10 (City surveys) * 0.50 = 1.25

CityTwo:    0 x YES .... 5 x NO .....% Population = 25%   --- > Factor = 25 (total surveys) / 10 (City surveys) * 0.25 = 0.625

CityThree:  5 x YES .... 0 x NO ......% Population = 25%   --- > Factor = 25 (total surveys) / 5 (City surveys) * 0.25 = 1.25

Here you can see exactly what It means....  As you can see, City 3 have the same City Weight as City One.
This is because it have 1/2 population, but we took also half surveys there.
Also, City 2, have exactly half Factor than City 1, because its half the population.

Also, City 2, have half factor than City 3, because we make half the surveys half the surveys on City 3.

Then, If I want to make a "TOTAL WEIGHTED YES", I would need to do the sum of :
2 Yes * CityOne Factor 1.25 = 2.5
0 Yes * CityTwo Factor 0.625 = 0
5 Yes * CityThree Factor 1.25 = 6.25

Total YES is 8.75  ... but we need %!
So, this would be :  8.75 / 25 = 0.35 =   35%  would be the WEIGHTED YES.

Doing the same calculation, without Weight, will give us :

2 Yes + 0 Yes + 5 Yes = 7 / 25 = 0.28 = 28%

As you see... there is a HUGE difference, not having the weight of each survey in mind.

So the City Factor .... is basically a number that represents " how much value have this survey based on population percentaje and the amount of surveys taken total and on this city ".

I think I explained a lot already... Hope someone can help me to make this on tableau.

Attached is TWBX file.

OH and FINALLY...... this works with MySQL.... so I cant use PIVOT!  (anyone have a easy workaround for this ?... pivot is very usefull for graphs using multiple questions... like you dont need to do the calculated fields if you can pivot the data.)

• ###### 1. Re: Charting Surveys:  How to weight by city factor?   HELP!

Update, it looks like, it calculated the factor okey using this formula  (the other was not counting all records, only per city, in both so it gave always 1 * poblacion% ... now I get correct  Total Records / City Records * Poblacion %

SUM({EXCLUDE [City] : COUNT([Number of Records])}) / SUM({INCLUDE [City] : COUNTD([ID])}) * ATTR([Poblacion %])

• ###### 2. Re: Charting Surveys:  How to weight by city factor?   HELP!

Hi Ariel,

Steve Wexler has done some work that's been valuable for me with regard to weighting in survey data -- he has a really nice detailed post here: Working with Weighted Survey Data – Data Revelations that might be of help. I'm not sure how he'd deal with the city factor element but I bet he'd be game to take a look at your workbook. :-) Good luck!