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?

    Michael Lawton

      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?
          Bill Lyons

          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]) }.

           

          If that doesn't help, please help us help you by attaching a packaged workbook.

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

            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?
              Norbert Maijoor

              Michael!

               

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

               

              billy.png

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

                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?
                  Norbert Maijoor

                  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?
                    Michael Lawton

                    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?
                      Norbert Maijoor

                      Bill Lyons

                       

                      Now we wait for the "Verdict";)

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

                        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?
                          Michael Lawton

                          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?
                            Bill Lyons

                            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?
                              Michael Lawton

                              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?
                                Bill Lyons

                                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?
                                  Michael Lawton

                                  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 LabelsSum of TOTHU_CYCount of JOIN_FIDHH
                                  50631396081745
                                  506431027231349
                                  5073131733632091
                                  5074256912569
                                  507523686131822
                                  5076871861453
                                  507774752322336
                                  507820384141456
                                  507919525111775
                                  5080999671428
                                  508120860201043
                                  508221808161363
                                  5083870661451
                                  508438955779
                                  5085727861213
                                  5086954991061
                                  50871130481413
                                  5088739271056
                                  508968248853
                                  5090801681002
                                  509115782789
                                  5092341431138
                                  509310210101021
                                  509410180101018
                                  509517028121419
                                  5096447241118
                                  5097848461414
                                  5098324931083
                                  509961204262354
                                  510022944161434
                                  510157066951
                                  5102830511755
                                  510317502875
                                  510469377991
                                  510532079171887
                                  5106956081195
                                  510737605752
                                  5108888081110
                                  510986409960
                                  511032011171883
                                  51111483515989
                                  511226463882
                                  511315122756
                                  511419605151307
                                  511516436141174
                                  511657126952
                                  511722218141587
                                  511825284141806
                                  511951150252046
                                  512023018171354
                                  512119162131474
                                  512216379111489
                                  512370754342081
                                  512447058222139
                                  512558212421386
                                  5126108498781391
                                  512795974472042
                                  512892256312976
                                  512970990312290
                                  513073080282610
                                  513164233391647
                                  5132117744442676
                                  5133140668443197
                                  5134168511685
                                  5135676041690
                                  513625312161582
                                  513797696323053
                                  513818359111669
                                  51392234792483
                                  514072825252913
                                  514120108111828
                                  5142429631432
                                  514327240151816
                                  5144999061665
                                  514533312162082
                                  514628405191495
                                  514722065151471
                                  514820293131561
                                  514928560161785
                                  515048526192554
                                  5151963281204
                                  515229214181623
                                  515346384261784
                                  5154748861248
                                  515553046884
                                  51561167612973
                                  515713832131064
                                  515830277171781
                                  5159871571245
                                  516023504131808
                                  516124400161525
                                  516212990101299
                                  516320340151356
                                  5164971671388
                                  516536475251459
                                  516630206221373
                                  516796390511890
                                  5168104252671556
                                  516941340301378
                                  517068338471454
                                  517181466461771
                                  517281098431886
                                  517323205151547
                                  517412430101243
                                  517521164131628
                                  517620111131547
                                  51771211491346
                                  517812386111126
                                  517975600401890
                                  51801887481071764
                                  51812052791031993
                                  51822150001251720
                                  518387759631393
                                  5184143744642246
                                  518565026611066
                                  518690282412202
                                  518731996191684
                                  518842039271557
                                  518924390151626
                                  5190123777512427
                                  519195082392438
                                  519218368141312
                                  5193835261392
                                  519485779953
                                  51951370614979
                                  519631200251248
                                  519713812121151
                                  519813178111198
                                  519913988131076
                                  5200701471002
                                  52011052191169
                                  520229424161839
                                  520326265151751
                                  520420925151395
                                  520547068281681
                                  520688569984
                                  520728995151933
                                  52081649791833
                                  520922983766
                                  5210665461109
                                  5211828171183
                                  52129531953
                                  52131067281334
                                  5214948571355
                                  52151157681447
                                  521615960101596
                                  521724393813
                                  52187761776
                                  5219669551339
                                  522014702735
                                  5221328831096
                                  5222385621928
                                  5223282621413
                                  522418551131427
                                  5225798551597
                                  522638484962
                                  522712882644
                                  52287071707
                                  522917042852
                                  5230863461439
                                  5231747061245
                                  5232896771281
                                  523329286488
                                  5234318331061
                                  5235591241478
                                  523625683856
                                  5237538051076
                                  5238238021190
                                  52408761876
                                  524121063702
                                  524248105962
                                  5243959071370
                                  524427084677
                                  524529133971
                                  5246551051102
                                  5247511241278
                                  5248588051176
                                  52495771577
                                  525016862843
                                  525132055641
                                  52531164012970
                                  5254642061070
                                  52552711271
                                  5256150528642352
                                  525717910101791
                                  52581461618812
                                  5259906442266
                                  526024670102467
                                  5261552631842
                                  526269307990
                                  526366888836
                                  526465850154390
                                  526554899134223
                                  526664232
                                  526832130152142
                                  526941865152791
                                  527069322223151
                                  527134281132637
                                  527226355527
                                  5273781051562
                                  52741250481563
                                  527567928849
                                  52761251614894
                                  527714641111331
                                  527832109211529
                                  527923829131833
                                  52801265462109
                                  52811115171593
                                  5282274221371
                                  52831268881586
                                  528435405708
                                  5285281421407
                                  5286277021385
                                  5287604832016
                                  528829733991
                                  528918602930
                                  5290367621838
                                  5291667532225
                                  5292596841492
                                  529314278111298
                                  5294897661496
                                  5295213621068
                                  5296402841007
                                  529730884772
                                  529854546909
                                  52991008081260
                                  53001943551051851
                                  530132820122735
                                  5302537051074
                                  5303550441376
                                  5304952010952
                                  5305833681042
                                  5306137711377
                                  530714366111306
                                  530816133131241
                                  53091008071440
                                  531071364381878
                                  531133020261270
                                  53121690482113
                                  5313539431798
                                  531426818112438
                                  5315912551825
                                  53161050771501
                                  53171330071900
                                  5318781271116
                                  5319594841487
                                  5320417031390
                                  5321723551447
                                  5322510041275
                                  5323587551175
                                  5324413131377
                                  5325694551389
                                  532617802890
                                  5327420041050
                                  5328868861448
                                  5329532231774
                                  53301151191279
                                  5331438041095
                                  5332277021385
                                  533318620101862
                                  533418410101841
                                  533516662833
                                  533649088321534
                                  533723472161467
                                  533817061111551
                                  533911264111024
                                  53401318481648
                                  5341281021405
                                  5342621051242
                                  5343433241083
                                  5344180611806
                                  5345892551785
                                  53462167201441505
                                  53472041741141791
                                  5348145913791847
                                  534917260101726
                                  5350293512935
                                  5352117520801469
                                  535392184691336
                                  5354122450791550
                                  535570350302345
                                  535641976241749
                                  535723244131788
                                  535879416362206
                                  535922021191159
                                  536096594342841
                                  5361206394822517
                                  5362128142542373
                                  536364764421542
                                  536434789191831
                                  536584916521633
                                  5366128900502578
                                  536770656641104
                                  5368159125672375
                                  536990108362503
                                  537062530371690
                                  537113387111217
                                  537214880101488
                                  53731170991301
                                  537430875191625
                                  537537332172196
                                  53761056371509
                                  5377939691044
                                  537812408111128
                                  5379883010883
                                  538036484912
                                  53811040611946
                                  538227989132153
                                  538317302865
                                  538419188131476
                                  538523552161472
                                  538645606760
                                  538721600151440
                                  5388814481018
                                  5389883261472
                                  5390749461249
                                  539147564232068
                                  5392623461039
                                  5393688861148
                                  539427213907
                                  539537524938
                                  539613351131027
                                  539718858141347
                                  539839955799
                                  5399948081185
                                  54001145681432
                                  540159166986
                                  5402746461244
                                  540365807940
                                  540426313877
                                  540566157945
                                  5406551051102
                                  54071072891192
                                  5408594051188
                                  540929253975
                                  54101084081355
                                  5411495641239
                                  54121006481258
                                  5413683051366
                                  541418250101825
                                  54155442272
                                  541614135111285
                                  541710070101007
                                  541846926782
                                  541926084652
                                  5420221421107
                                  542112430101243
                                  542262088776
                                  542342805856
                                  542414469131113
                                  5425692412577
                                  542656580411380
                                  542733684842
                                  5429816061360
                                  5430227621138
                                  543116122806
                                  543210910101091
                                  5433470041175
                                  543447255945
                                  5435105011050
                                  54387401740
                                  54391063891182
                                  5440339931133
                                  5441415641039
                                  5442320731069
                                  5443502441256
                                  544416002800
                                  5445510551021
                                  5446117811178
                                  5447540441351
                                  54487871787
                                  5449205221026
                                  545042246704
                                  5451572551145
                                  5452819771171
                                  545316050101605
                                  54541375291528
                                  5455574441436
                                  545627859132143
                                  5457665551331
                                  5458303821519
                                  5459278021390
                                  5460434431448
                                  546135604890
                                  5462988471412
                                  546336524913
                                  5464437641094
                                  54651033552067
                                  5466862861438
                                  546718270101827
                                  546818106111646
                                  546949755995
                                  547033136162071
                                  547112947111177
                                  547235895152393
                                  54739261926
                                  5474974481218
                                  54751225771751
                                  54761050771501
                                  5477571051142
                                  5478458841147
                                  5479818051636
                                  548020856121738
                                  548176008950
                                  5482774441936
                                  54831491072130
                                  548448496163031
                                  54851975282469
                                  5486762051524
                                  5487555641389
                                  548835411172083
                                  5489563051126
                                  549129853995
                                  5492802261337
                                  5493374421872
                                  5494108211082
                                  5495511051022
                                  5497236621183
                                  54998741874
                                  5501134611346
                                  5504909242273
                                  5505552441381
                                  5506117711177
                                  5507181111811
                                  550838084952
                                  5509345031150
                                  5510218021090
                                  55118451845
                                  551230124753
                                  55139801980
                                  551435244881
                                  551529883996
                                  55168841884
                                  5517446441116
                                  551927873929
                                  5520135411354
                                  5524170211702
                                  55482477071531619
                                  55495766093031903
                                  55504184402201902
                                  55512598921721511
                                  55523198401601999
                                  5568114037771481
                                  556939405371065
                                  557093328382456
                                  55711628
                                  557223998131846
                                  557318150151210
                                  55741502272146
                                  55763384143787
                                  557725730102573
                                  557891168372464
                                  557954072361502
                                  558056610301887
                                  5581186728682746
                                  5582215414713034
                                  5583184260603071
                                  5584232612326
                                  55881080042700
                                  559242636331292
                                  559336309191911
                                  559485869471827
                                  55954249351712485
                                  55962612021222141
                                  5597140086891574
                                  55982997102051462
                                  559920558191082
                                  560011480101148
                                  560155712321741
                                  560261560361710
                                  560332472181804
                                  560452762232294
                                  5605419431398
                                  560622932131764
                                  560717676121473
                                  560878058312518
                                  560928560201428
                                  56101886719993
                                  561119722191038
                                  5613637532125
                                  561762286212966
                                  561842063212003
                                  561923738131826
                                  562056700202835
                                  5693188511885
                                  5698404622023
                                  6131131011310
                                  6252255221276
                                  625525644641
                                  6256734441836
                                  6257252221261
                                  6258744661241
                                  62615861586
                                  6262153711537
                                  626318902945
                                  6265234621173
                                  6266416131387
                                  6267256621283
                                  6268201221006
                                  6269126711267
                                  6270348331161
                                  6271399631332
                                  6272776051552
                                  627316522826
                                  627431684792
                                  6275336331121
                                  62778831883
                                  627855386923
                                  6279560041400
                                  6280403231344
                                  6281399931333
                                  6282718841797
                                  6283338431128
                                  6289588051176
                                  629059846461301
                                  629161275571075
                                  629226708930
                                  6293584051168
                                  62948201820
                                  629514462723
                                  6296947281184
                                  6297653051306
                                  629813762688
                                  6299225821129
                                  6300499531665
                                  630233355667
                                  630326223874
                                  63041049681312
                                  63056481648
                                  630631507450
                                  6307206221031
                                  630814242712
                                  63096981698
                                  631013743458
                                  631118122906
                                  6312363031210
                                  6313524051048
                                  631445546759
                                  631610322516
                                  63179862493
                                  6318227221136
                                  6319729551459
                                  63201070461784
                                  6321102811028
                                  632217402870
                                  6323518051036
                                  6324861551723
                                  6327212621063
                                  6328450331501
                                  632919662983
                                  6330532041330
                                  6331310531035
                                  633244648558
                                  63331368991521
                                  6334205021025
                                  633626403880
                                  633713596111236
                                  63388351835
                                  6340102011020
                                  634129853995
                                  6342224421122
                                  6343260421302
                                  6344106911069
                                  63467171717
                                  6347718261197
                                  634828053935
                                  6349454831516
                                  6350211021055
                                  635132844821
                                  6352538231794
                                  6355148311483
                                  6828198018573474
                                  6829180435692615
                                  6830338647734639
                                  6831241628584166
                                  6832346096973568
                                  6833141650502833
                                  6834125911373403
                                  68357543643562119
                                  68362951812291289
                                  68373696251252957
                                  6838148833692157
                                  6839148770871710
                                  68404378501263475
                                  68416527402202967
                                  687351660212460
                                  687453086222413
                                  70502847
                                  7051140270
                                  2008707110792853100
                                  • 14. Re: Can anyone help me figure out how to do this?
                                    Norbert Maijoor

                                    Michael!

                                     

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

                                     

                                    mopo.png

                                    1 2 Previous Next