0 Replies Latest reply on Nov 28, 2018 5:52 PM by Xavier Pettigrew

    Problems calculating a percentage from fields in separate tables

    Xavier Pettigrew

      Hi,

       

      Apologies that I can’t post a twbx, due to data confidentiality, but let me try to explain. I’m a novice with calculated fields, so it’s easy enough to lay out the problem, because for most of you, it’s probably simple.

       

      I have two data sources, let’s call them STUFF and THINGS. Each contains numerous data fields, some of which appear in both sources, but most are specific to a single table.

       

      So, I’m located in the STUFF table, and want to create a calculated field to produce a percentage, where the numerator field (let’s call it field1) is in THINGS, but the denominator (let’s call it field2) is in STUFF (where I am).

       

      There’s a condition to be factored in. I only want to include some of the possible records of field1 in the calculation. Field1 stores values of “t” for true, and “f” for false. I’m only interested in the true values.

       

      As far as my limited knowledge takes me, this should look something like:

       

      COUNT ( [THINGS].[field1] = "t" ) / COUNT ( [field2] )

       

      I know about setting the Default properties/Number format of the calculated field to Percentage, so I believe I don’t need to explicitly multiply by 100. I think COUNT is the appropriate function, as the records in field2 are by definition unique.

       

      Desktop tells me this is a valid formulation, but there are two problems when I use the calculated field:

       

      First, I get results for both “t” and “f”, as opposed to just the “t” values, which is what I want to count. Second, the “percentages” returned are wildly off where they should be. I suspect the way I’ve expressed the “t”-only condition to be a cause of the first problem. I don’t know what the second problem is caused by, unless it’s something to do with the way I’ve identified the numerator field, which is located in the other table. Or should I be building a formal connection between the two tables, which I have not consciously done, assuming that [THINGS].[field1] is an adequate pointer from one table to the second.

       

      I’m using Tableau Desktop 2018.2.

       

      Can anyone help?

       

      Thanks.

       

      XP.