4 Replies Latest reply on Jun 14, 2017 6:58 PM by Shinichiro Murakami

    Trouble with getting Totals

    Marcos Cardoso

      Hi,

       

      I was curious of there was a way to sum two values that depend on two different filters. Maybe I am over thinking a solution - maybe somebody can help.

       

      I have two columns-

       

      1. Immigration to City

      Includes four cities - Los Angeles, Las Vegas, San Fransisco, San Diego

       

      2. Immigration from City

      Includes four cities - Los Angeles, Las Vegas, San Fransisco, San Diego

       

       

      We can assume data has the following columns and they are all filled.

       

      Name of Person

      Month

      From City

      To City

       

      Let's keep it simple and pretend our data set only includes

      Tom, June 2017, Los Angeles, San Diego.

      Sam, June 2017, Los Angeles, San Fransisco

      Jon, June 2017, San Diego, Los Angeles

      Bob, June 2017, Las Vegas, San Fransisco

       

       

      The Problem: My data set in reality has over 30,000 records and I need to add/subtract the movement from the different cities similar to this.

      - When I filter a city let's say

       

      The user needs to be able to select a city - let's say they select Los Angeles

      - When they filter for Los Angeles it needs to show

       

      To Los Angeles        Away from Los Angeles   Subtotal

      1                                 -2                                        -1

       

       

      - When they filter for San Diego it needs to show

       

      To San Diego      Away from San Diego   Subtotal

      1                          -1                                     0

       

      - When they filter for Las Vegas it needs to show

       

      To Las Vegas      Away from Las Vegas   Subtotal

      0                          -1                                     -1

       

      - When they filter for San Francisco it needs to show

       

      To SF      Away from SF   Subtotal

      2             0                         2

       

       

      Similar to this -

       

      Any Insights would be appreciated thank you so much!