0 Replies Latest reply on Mar 25, 2020 3:23 PM by Mark Wilber

    Copy of column totals results in perverse splitting by thousands

    Mark Wilber

      I have a data set which has 3 (relevant) columns: trip_id, from_station_id and to_station_id. I can make a table showing counts of trips to and from given station_ids, where reading down a column show the trips to that station from the station listed on the left, and reading across rows show trips from a station to the stations above. Diagonal elements show round trips to and from the same station_id.

       

      By creating grand totals across columns  we get totals of departures from each station. So far, so good.

      If I copy and transpose the table, and create grand totals across columns, the totals are now for arrivals at each station. Also good.

      I want the differences, for net traffic at each station, so I thought I would copy each grand total column and subtract.

       

      The problem: the copies are perverse:

       

      original:

      copy:

      There are now two columns, with the left column indicating thousands, and the right column ordinary counts except when the total counts are less than 1000. The total of all trips ("All") is 236 × 1000 + 65 = 236065. The total of all trips originating from the first listed station is not 2 × 1000, but instead is just 2. (It's this last part that makes the splitting 'perverse'.) Hoping that the problem was due to formatted commas in the > 1000 values, I removed them from all number formats (as shown), with no change in overall results. (I shudder to think what might happen if grand totals summed to more than a billion.)

       

      Is there a "correct" way to copy the grand total columns to avoid this? Is there a way of simply subtracting grand total column values from the separate tables?

       

      Optimistically, I started over and created two calculated variables:

       

      Arrivals by Station: {FIXED [To Station Id]:COUNT[Trip Id]}

      Departures from Station: {FIXED [From Station Id]:COUNT[Trip Id]}

       

      SUM(Arrivals by Station) and SUM(Departures by Station) also yield the sums I am looking for, but copies of them simply drop parts of the count > 1000 (i.e., results are modulo 1000).

       

      Finally, editing in shelf, I created SUM([Arrivals by Station]) - SUM([Departures by Station]), which yielded a solitary 0, probably due to confusion about matching text of From Station Id with To Station Id. Am I asking too much of Tableau?