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

# Trouble with getting Totals

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!

• ###### 1. Re: Trouble with getting Totals

Hi Marcos

I have a question - how are you creating the separate filters (or conditions) based on the to and from cities -

The to and from are separate fields and EACH needs to have a condition built around them

EG          If [tocity] ='SF' AND [fromcity] = 'SF' then sum(numberofrecordes .....represents one pair of to/from cities - you would need to build a statement around each possible pair

OR you could to it with filters where the user enters a to city and a from city and set a t/f filter on the to cities and one on your from cities and set them to True

Let me know if this helps

Jim

• ###### 2. Re: Trouble with getting Totals

Hi Marcos

In this you should pivot data from "Edit Data source"

==> From / To

==> City

Showing "0" on all null cells requires another very complicated formula setting.

If blank still works, that's most preferable.

Thanks,

Shin

• ###### 3. Re: Trouble with getting Totals

hi,
Thank you so much for your response!

I'm unable to pivot columns due to the source being SQL server , is there a work around?

Thank you

• ###### 4. Re: Trouble with getting Totals

SELECT

'project_start_date' as [pivot_field_name],

[project_start_date] as [pivot_field_value],

[...] --// all other field names from your table

FROM [your_table]

UNION ALL

SELECT

'project_end_date' as [pivot_field_name],

[project_end_date] as [pivot_field_value],

[...] --// all other field names from your table

FROM [your_table]

Thanks,

Ahin