1 2 Previous Next 18 Replies Latest reply on Jan 2, 2016 12:45 PM by Bill Lyons

# Can anyone help me figure out how to do this?

I have a table of customer records.  For each customer record I have the census
tract number and the number of households in that census tract.  I'm trying to sum the number of households within a branch.  I just can't add all the households for all the census tracts for all customers within a branch because I would be adding all the households from the duplicate census tracts.  So I’m trying to divide the number of households by the count of census tracts to get the correct number of households per census tract, then sum this total for all the individual distinct census tracts within my aggregation dimension which happens to be branch.  I can’t get the calculated field to work.

my email is mlawton@west-ext.com

The name is Michael

• ###### 1. Re: Can anyone help me figure out how to do this?

It is difficult to give a definitive answer without a sample. Can you attach a packaged workbook showing the fields and the nature of the problem? See Tableau Forum Guidelines, Posting a Perfect Question |Tableau Support Community , and Anonymize your Tableau Package Data for Sharing |Tableau Support Community for more information.

My best guess would be to use an LoD expression like { FIXED [Census Tract Number] : MIN([Households in Census Tract]) }.

1 of 1 people found this helpful
• ###### 2. Re: Can anyone help me figure out how to do this?

Bill, thanks.  Here is the data.

The join fid column is the census tract.  SC are my branches.  1LM-Affluent

Estates is one of the columns containing the number of households for that

consumer marketing group.  I'm trying to total the sum of the households

for that group by SC but only adding the sums from the distinct set of join

fid tract numbers.

On Thu, Dec 31, 2015 at 5:09 PM, Bill Lyons <

• ###### 3. Re: Can anyone help me figure out how to do this?

Michael!

Find my approach based on a simple example (LOD-espressions)  in attached workbook version 9.1

• ###### 4. Re: Can anyone help me figure out how to do this?

Excellent. Thank you for the sample. This is exactly as I expected. To solve this, I created calculated field [1LM-Affluent Estates by Census Tract]:

{ FIXED [Join Fid] : min([1LM-Affluent Estates]) }

In the attached example, the first sheet is by Census Tract ([Join Fid]). The columns show the SUM([1LM-Affluent Estates]), which as you know, is wrong, and SUM([1LM-Affluent Estates by Census Tract]), which is correct. It also includes the number of records for each census tract, and you can see that the SUM([1LM-Affluent Estates]) is the number of records multiplied by SUM([1LM-Affluent Estates by Census Tract]), proving the math. You can also look at the underlying data to verify it.

The second sheet has these same sums, grouped by SC. There is only one SC (presumably because this is a small sample data set), but you can see that the totals match the grand total at the bottom of the first sheet.

My attached example is in v9.2, which I know is an upgrade from your version. At the moment I only have 9.2 on this machine. I can reproduce this in a lower version if you tell me what version you need so I know what version to install. However, I recommend you upgrade if you can. Keep in mind that LoD expressions will only work in v9.0 or higher.

Questions?

• ###### 5. Re: Can anyone help me figure out how to do this?

Bill!

Attached your workbook in version 9.1

1 of 1 people found this helpful
• ###### 6. Re: Can anyone help me figure out how to do this?

Dear Norbert

Thanks so much, but using your example this is what I'm trying to do.

Let's say you had another column for the number of people that worked or

lived at each one of the names.  And let's say you had duplicates in this

name column.  I am trying to sum the number of people that work or live in

each city but not count the duplicates.  So basically I would be trying to

sum the number of people per city, then count the distinct # of names -

divide this number of people by the count of distinct names; then add all

these totals to get the total number of people per city?  Make sense?

On Fri, Jan 1, 2016 at 3:45 AM, Norbert Maijoor <

• ###### 7. Re: Can anyone help me figure out how to do this?

Now we wait for the "Verdict";)

• ###### 8. Re: Can anyone help me figure out how to do this?

Bill

Brilliant!  The numbers are right on.  I had done this manually using

Excel.  Thank you so much.  I had submitted this to Tableau a week ago.

The normal support person couldn't figure it out and had to submit it to a

senior engineer.  It hasn't been assigned to a senior person yet.

What do you do for a living and how long have you been using Tableau.

Michael

On Thu, Dec 31, 2015 at 5:09 PM, Bill Lyons <

• ###### 9. Re: Can anyone help me figure out how to do this?

Again, thanks so much Bill.

On Fri, Jan 1, 2016 at 9:10 AM, Bill Lyons <

• ###### 10. Re: Can anyone help me figure out how to do this?

Always happy to help! And welcome to the Tableau Forums!

I am a Data Scientist (which you can see in my profile). I have been using Tableau for about 2.5 years. I took the Jedi class at TC15, and went to other Jedi-level sessions on LoD expressions there. LoDs are amazing! This just scratched the surface! As you may have noticed, I am a huge Tableau Forums junkie. I have learned more here than all the classes and videos put together. I hope you will explore more, and as you learn, consider paying-it-forward (see Crow's Nest Love: Pay It Forward! |Tableau Support Community) .

Depending on your data source, you might want to consider pivoting your market segmentation columns, so you have one dimension with the market segment name, and one measure for the household count. Then, you can do all of the market segments with a single LoD calc, instead of creating multiple calculations. If your data is in Excel, you can pivot very quickly in the Data Source editor. In SQL, use the UNPIVOT command. PIVOT and UNPIVOT in Sql Server | SqlHints.com can help you with that.

Also, as you will see in Tableau Forum Guidelines , it is customary to mark helpful and/or correct answers. This helps others who may be searching the forums know if they have found good solutions.

Happy New Year!

1 of 1 people found this helpful
• ###### 11. Re: Can anyone help me figure out how to do this?

Thanks Bill.  I may check out pivoting at the source level because using

your example I created the same expression for the number of current

households.  So now I have the correct number of Affluent Estates'

households and the correct number of current households per Service Center

(SC).  But when I divide the number of Affluent Estate households into the

current number of households I don't get the right percentage.  For

example, using SC 1 (Los Angeles) the expression you gave me earlier

calculated 28,304 which is correct.  Using the same expression design for

current number of households I get 790,858 which is also correct.  If you

divide the two you're supposed to get 3.58%.  But I get 17.9%?

On Fri, Jan 1, 2016 at 10:32 AM, Bill Lyons <

1 of 1 people found this helpful
• ###### 12. Re: Can anyone help me figure out how to do this?

Sounds like perhaps something else may be going on in the data. For example, some household are being counted in more than one market segment, or aren't being counted in any segment. What field is the "current number of households," and how do you know what percentage you should get?

Pivoting will definitely help track down these kinds of problems.

• ###### 13. Re: Can anyone help me figure out how to do this?

The total number of households are in the field called TOTHU_CY (total households current year).  I used your first expression but substituted this field name to get the correct total of households for that SC - SC 1 for example which is my Los Angeles service center.  I know this because I extracted just SC 1's data in Excel and pivoted on join fid, sum the number of current housedholds, summed the counts of join fid, divided the total number of households by the total counts of join fid to get the total number of households per join fid.  Then I totaled this number of households per join fid.

 Row Labels Sum of TOTHU_CY Count of JOIN_FID HH 5063 13960 8 1745 5064 31027 23 1349 5073 131733 63 2091 5074 2569 1 2569 5075 23686 13 1822 5076 8718 6 1453 5077 74752 32 2336 5078 20384 14 1456 5079 19525 11 1775 5080 9996 7 1428 5081 20860 20 1043 5082 21808 16 1363 5083 8706 6 1451 5084 3895 5 779 5085 7278 6 1213 5086 9549 9 1061 5087 11304 8 1413 5088 7392 7 1056 5089 6824 8 853 5090 8016 8 1002 5091 1578 2 789 5092 3414 3 1138 5093 10210 10 1021 5094 10180 10 1018 5095 17028 12 1419 5096 4472 4 1118 5097 8484 6 1414 5098 3249 3 1083 5099 61204 26 2354 5100 22944 16 1434 5101 5706 6 951 5102 8305 11 755 5103 1750 2 875 5104 6937 7 991 5105 32079 17 1887 5106 9560 8 1195 5107 3760 5 752 5108 8880 8 1110 5109 8640 9 960 5110 32011 17 1883 5111 14835 15 989 5112 2646 3 882 5113 1512 2 756 5114 19605 15 1307 5115 16436 14 1174 5116 5712 6 952 5117 22218 14 1587 5118 25284 14 1806 5119 51150 25 2046 5120 23018 17 1354 5121 19162 13 1474 5122 16379 11 1489 5123 70754 34 2081 5124 47058 22 2139 5125 58212 42 1386 5126 108498 78 1391 5127 95974 47 2042 5128 92256 31 2976 5129 70990 31 2290 5130 73080 28 2610 5131 64233 39 1647 5132 117744 44 2676 5133 140668 44 3197 5134 1685 1 1685 5135 6760 4 1690 5136 25312 16 1582 5137 97696 32 3053 5138 18359 11 1669 5139 22347 9 2483 5140 72825 25 2913 5141 20108 11 1828 5142 4296 3 1432 5143 27240 15 1816 5144 9990 6 1665 5145 33312 16 2082 5146 28405 19 1495 5147 22065 15 1471 5148 20293 13 1561 5149 28560 16 1785 5150 48526 19 2554 5151 9632 8 1204 5152 29214 18 1623 5153 46384 26 1784 5154 7488 6 1248 5155 5304 6 884 5156 11676 12 973 5157 13832 13 1064 5158 30277 17 1781 5159 8715 7 1245 5160 23504 13 1808 5161 24400 16 1525 5162 12990 10 1299 5163 20340 15 1356 5164 9716 7 1388 5165 36475 25 1459 5166 30206 22 1373 5167 96390 51 1890 5168 104252 67 1556 5169 41340 30 1378 5170 68338 47 1454 5171 81466 46 1771 5172 81098 43 1886 5173 23205 15 1547 5174 12430 10 1243 5175 21164 13 1628 5176 20111 13 1547 5177 12114 9 1346 5178 12386 11 1126 5179 75600 40 1890 5180 188748 107 1764 5181 205279 103 1993 5182 215000 125 1720 5183 87759 63 1393 5184 143744 64 2246 5185 65026 61 1066 5186 90282 41 2202 5187 31996 19 1684 5188 42039 27 1557 5189 24390 15 1626 5190 123777 51 2427 5191 95082 39 2438 5192 18368 14 1312 5193 8352 6 1392 5194 8577 9 953 5195 13706 14 979 5196 31200 25 1248 5197 13812 12 1151 5198 13178 11 1198 5199 13988 13 1076 5200 7014 7 1002 5201 10521 9 1169 5202 29424 16 1839 5203 26265 15 1751 5204 20925 15 1395 5205 47068 28 1681 5206 8856 9 984 5207 28995 15 1933 5208 16497 9 1833 5209 2298 3 766 5210 6654 6 1109 5211 8281 7 1183 5212 953 1 953 5213 10672 8 1334 5214 9485 7 1355 5215 11576 8 1447 5216 15960 10 1596 5217 2439 3 813 5218 776 1 776 5219 6695 5 1339 5220 1470 2 735 5221 3288 3 1096 5222 3856 2 1928 5223 2826 2 1413 5224 18551 13 1427 5225 7985 5 1597 5226 3848 4 962 5227 1288 2 644 5228 707 1 707 5229 1704 2 852 5230 8634 6 1439 5231 7470 6 1245 5232 8967 7 1281 5233 2928 6 488 5234 3183 3 1061 5235 5912 4 1478 5236 2568 3 856 5237 5380 5 1076 5238 2380 2 1190 5240 876 1 876 5241 2106 3 702 5242 4810 5 962 5243 9590 7 1370 5244 2708 4 677 5245 2913 3 971 5246 5510 5 1102 5247 5112 4 1278 5248 5880 5 1176 5249 577 1 577 5250 1686 2 843 5251 3205 5 641 5253 11640 12 970 5254 6420 6 1070 5255 271 1 271 5256 150528 64 2352 5257 17910 10 1791 5258 14616 18 812 5259 9064 4 2266 5260 24670 10 2467 5261 5526 3 1842 5262 6930 7 990 5263 6688 8 836 5264 65850 15 4390 5265 54899 13 4223 5266 64 2 32 5268 32130 15 2142 5269 41865 15 2791 5270 69322 22 3151 5271 34281 13 2637 5272 2635 5 527 5273 7810 5 1562 5274 12504 8 1563 5275 6792 8 849 5276 12516 14 894 5277 14641 11 1331 5278 32109 21 1529 5279 23829 13 1833 5280 12654 6 2109 5281 11151 7 1593 5282 2742 2 1371 5283 12688 8 1586 5284 3540 5 708 5285 2814 2 1407 5286 2770 2 1385 5287 6048 3 2016 5288 2973 3 991 5289 1860 2 930 5290 3676 2 1838 5291 6675 3 2225 5292 5968 4 1492 5293 14278 11 1298 5294 8976 6 1496 5295 2136 2 1068 5296 4028 4 1007 5297 3088 4 772 5298 5454 6 909 5299 10080 8 1260 5300 194355 105 1851 5301 32820 12 2735 5302 5370 5 1074 5303 5504 4 1376 5304 9520 10 952 5305 8336 8 1042 5306 1377 1 1377 5307 14366 11 1306 5308 16133 13 1241 5309 10080 7 1440 5310 71364 38 1878 5311 33020 26 1270 5312 16904 8 2113 5313 5394 3 1798 5314 26818 11 2438 5315 9125 5 1825 5316 10507 7 1501 5317 13300 7 1900 5318 7812 7 1116 5319 5948 4 1487 5320 4170 3 1390 5321 7235 5 1447 5322 5100 4 1275 5323 5875 5 1175 5324 4131 3 1377 5325 6945 5 1389 5326 1780 2 890 5327 4200 4 1050 5328 8688 6 1448 5329 5322 3 1774 5330 11511 9 1279 5331 4380 4 1095 5332 2770 2 1385 5333 18620 10 1862 5334 18410 10 1841 5335 1666 2 833 5336 49088 32 1534 5337 23472 16 1467 5338 17061 11 1551 5339 11264 11 1024 5340 13184 8 1648 5341 2810 2 1405 5342 6210 5 1242 5343 4332 4 1083 5344 1806 1 1806 5345 8925 5 1785 5346 216720 144 1505 5347 204174 114 1791 5348 145913 79 1847 5349 17260 10 1726 5350 2935 1 2935 5352 117520 80 1469 5353 92184 69 1336 5354 122450 79 1550 5355 70350 30 2345 5356 41976 24 1749 5357 23244 13 1788 5358 79416 36 2206 5359 22021 19 1159 5360 96594 34 2841 5361 206394 82 2517 5362 128142 54 2373 5363 64764 42 1542 5364 34789 19 1831 5365 84916 52 1633 5366 128900 50 2578 5367 70656 64 1104 5368 159125 67 2375 5369 90108 36 2503 5370 62530 37 1690 5371 13387 11 1217 5372 14880 10 1488 5373 11709 9 1301 5374 30875 19 1625 5375 37332 17 2196 5376 10563 7 1509 5377 9396 9 1044 5378 12408 11 1128 5379 8830 10 883 5380 3648 4 912 5381 10406 11 946 5382 27989 13 2153 5383 1730 2 865 5384 19188 13 1476 5385 23552 16 1472 5386 4560 6 760 5387 21600 15 1440 5388 8144 8 1018 5389 8832 6 1472 5390 7494 6 1249 5391 47564 23 2068 5392 6234 6 1039 5393 6888 6 1148 5394 2721 3 907 5395 3752 4 938 5396 13351 13 1027 5397 18858 14 1347 5398 3995 5 799 5399 9480 8 1185 5400 11456 8 1432 5401 5916 6 986 5402 7464 6 1244 5403 6580 7 940 5404 2631 3 877 5405 6615 7 945 5406 5510 5 1102 5407 10728 9 1192 5408 5940 5 1188 5409 2925 3 975 5410 10840 8 1355 5411 4956 4 1239 5412 10064 8 1258 5413 6830 5 1366 5414 18250 10 1825 5415 544 2 272 5416 14135 11 1285 5417 10070 10 1007 5418 4692 6 782 5419 2608 4 652 5420 2214 2 1107 5421 12430 10 1243 5422 6208 8 776 5423 4280 5 856 5424 14469 13 1113 5425 6924 12 577 5426 56580 41 1380 5427 3368 4 842 5429 8160 6 1360 5430 2276 2 1138 5431 1612 2 806 5432 10910 10 1091 5433 4700 4 1175 5434 4725 5 945 5435 1050 1 1050 5438 740 1 740 5439 10638 9 1182 5440 3399 3 1133 5441 4156 4 1039 5442 3207 3 1069 5443 5024 4 1256 5444 1600 2 800 5445 5105 5 1021 5446 1178 1 1178 5447 5404 4 1351 5448 787 1 787 5449 2052 2 1026 5450 4224 6 704 5451 5725 5 1145 5452 8197 7 1171 5453 16050 10 1605 5454 13752 9 1528 5455 5744 4 1436 5456 27859 13 2143 5457 6655 5 1331 5458 3038 2 1519 5459 2780 2 1390 5460 4344 3 1448 5461 3560 4 890 5462 9884 7 1412 5463 3652 4 913 5464 4376 4 1094 5465 10335 5 2067 5466 8628 6 1438 5467 18270 10 1827 5468 18106 11 1646 5469 4975 5 995 5470 33136 16 2071 5471 12947 11 1177 5472 35895 15 2393 5473 926 1 926 5474 9744 8 1218 5475 12257 7 1751 5476 10507 7 1501 5477 5710 5 1142 5478 4588 4 1147 5479 8180 5 1636 5480 20856 12 1738 5481 7600 8 950 5482 7744 4 1936 5483 14910 7 2130 5484 48496 16 3031 5485 19752 8 2469 5486 7620 5 1524 5487 5556 4 1389 5488 35411 17 2083 5489 5630 5 1126 5491 2985 3 995 5492 8022 6 1337 5493 3744 2 1872 5494 1082 1 1082 5495 5110 5 1022 5497 2366 2 1183 5499 874 1 874 5501 1346 1 1346 5504 9092 4 2273 5505 5524 4 1381 5506 1177 1 1177 5507 1811 1 1811 5508 3808 4 952 5509 3450 3 1150 5510 2180 2 1090 5511 845 1 845 5512 3012 4 753 5513 980 1 980 5514 3524 4 881 5515 2988 3 996 5516 884 1 884 5517 4464 4 1116 5519 2787 3 929 5520 1354 1 1354 5524 1702 1 1702 5548 247707 153 1619 5549 576609 303 1903 5550 418440 220 1902 5551 259892 172 1511 5552 319840 160 1999 5568 114037 77 1481 5569 39405 37 1065 5570 93328 38 2456 5571 16 2 8 5572 23998 13 1846 5573 18150 15 1210 5574 15022 7 2146 5576 33841 43 787 5577 25730 10 2573 5578 91168 37 2464 5579 54072 36 1502 5580 56610 30 1887 5581 186728 68 2746 5582 215414 71 3034 5583 184260 60 3071 5584 2326 1 2326 5588 10800 4 2700 5592 42636 33 1292 5593 36309 19 1911 5594 85869 47 1827 5595 424935 171 2485 5596 261202 122 2141 5597 140086 89 1574 5598 299710 205 1462 5599 20558 19 1082 5600 11480 10 1148 5601 55712 32 1741 5602 61560 36 1710 5603 32472 18 1804 5604 52762 23 2294 5605 4194 3 1398 5606 22932 13 1764 5607 17676 12 1473 5608 78058 31 2518 5609 28560 20 1428 5610 18867 19 993 5611 19722 19 1038 5613 6375 3 2125 5617 62286 21 2966 5618 42063 21 2003 5619 23738 13 1826 5620 56700 20 2835 5693 1885 1 1885 5698 4046 2 2023 6131 1310 1 1310 6252 2552 2 1276 6255 2564 4 641 6256 7344 4 1836 6257 2522 2 1261 6258 7446 6 1241 6261 586 1 586 6262 1537 1 1537 6263 1890 2 945 6265 2346 2 1173 6266 4161 3 1387 6267 2566 2 1283 6268 2012 2 1006 6269 1267 1 1267 6270 3483 3 1161 6271 3996 3 1332 6272 7760 5 1552 6273 1652 2 826 6274 3168 4 792 6275 3363 3 1121 6277 883 1 883 6278 5538 6 923 6279 5600 4 1400 6280 4032 3 1344 6281 3999 3 1333 6282 7188 4 1797 6283 3384 3 1128 6289 5880 5 1176 6290 59846 46 1301 6291 61275 57 1075 6292 2670 89 30 6293 5840 5 1168 6294 820 1 820 6295 1446 2 723 6296 9472 8 1184 6297 6530 5 1306 6298 1376 2 688 6299 2258 2 1129 6300 4995 3 1665 6302 3335 5 667 6303 2622 3 874 6304 10496 8 1312 6305 648 1 648 6306 3150 7 450 6307 2062 2 1031 6308 1424 2 712 6309 698 1 698 6310 1374 3 458 6311 1812 2 906 6312 3630 3 1210 6313 5240 5 1048 6314 4554 6 759 6316 1032 2 516 6317 986 2 493 6318 2272 2 1136 6319 7295 5 1459 6320 10704 6 1784 6321 1028 1 1028 6322 1740 2 870 6323 5180 5 1036 6324 8615 5 1723 6327 2126 2 1063 6328 4503 3 1501 6329 1966 2 983 6330 5320 4 1330 6331 3105 3 1035 6332 4464 8 558 6333 13689 9 1521 6334 2050 2 1025 6336 2640 3 880 6337 13596 11 1236 6338 835 1 835 6340 1020 1 1020 6341 2985 3 995 6342 2244 2 1122 6343 2604 2 1302 6344 1069 1 1069 6346 717 1 717 6347 7182 6 1197 6348 2805 3 935 6349 4548 3 1516 6350 2110 2 1055 6351 3284 4 821 6352 5382 3 1794 6355 1483 1 1483 6828 198018 57 3474 6829 180435 69 2615 6830 338647 73 4639 6831 241628 58 4166 6832 346096 97 3568 6833 141650 50 2833 6834 125911 37 3403 6835 754364 356 2119 6836 295181 229 1289 6837 369625 125 2957 6838 148833 69 2157 6839 148770 87 1710 6840 437850 126 3475 6841 652740 220 2967 6873 51660 21 2460 6874 53086 22 2413 7050 28 4 7 7051 140 2 70 20087071 10792 853100
• ###### 14. Re: Can anyone help me figure out how to do this?

Michael!

Find my approach  based on your feedback in attached workbook version 9.1

1 2 Previous Next