Filter Indirectly Affecting Fields

[UncleRiotous]

Apologies if this is a bit long but I’m having a major problem with a filter on a sheet where it’s affecting items indirectly that I don’t want affected. I’ve created a small test app to check my logic and hopefully explain to others what I’m struggling with.

I have the following data loaded and I have created a sheet that sums all the values of items for Type A and Type D. I’ve also included filters onto the sheet.

Project	Location	Type	Hold	Items
1	A	A		55
2	A	D	Y	69
3	A	D	Y	70
4	A	D	N	71
5	A	D	N	72
6	B	A		45
7	B	D	Y	28
8	B	D	Y	29
9	B	D	N	30
10	B	D	N	31
11	C	A		102
12	C	D	Y	111
13	C	D	N	111
14	C	D	N	111
15	C	D	N	111
16	D	A		123
17	D	D	N	129
18	D	D	N	130
19	D	D	N	131
20	D	D	N	132

The problem I’m having is that when I filter for Projects which are On Hold (i.e. Hold=Y) then Location D vanishes from all calculations because it doesn’t have any Projects that are On Hold.

With No Filters (screenshot)

Unfiltered

With Hold=Y (screenshot)

Filtered

The Sum of D is correct in both cases but the Sum of Type A should remain as 325 regardless of whether Hold is filtered.

Expression for Sum of Type D

• Sum({1<Type={'D'}>}Items)

Expression for Sum of Type D (Filter on Location)

• Sum({1<Type={'D'},Location={$(vSelectedLocation)}>}Items)

Expression for Sum of Type A

• Sum({1<Type={'A'}>}Items)

Expression for Sum of Type A (Filter on Location)

• Sum({1<Type={'A'},Location={$(vSelectedLocation)}>}Items)

The variable vSelectedLocation

• =if(getselectedcount(Location)=0,
  chr(39)& concat(DISTINCT {<BID=>}Location,chr(39)&', '&chr(39))&chr(39),
  chr(39)& getfieldselections(Location,chr(39)&', '&chr(39))&chr(39))

I need to be able to filter for On Hold jobs and compare them to the numbers of Type A work (which need to be neither On Hold or not On Hold) but I don’t want it to reduce the number of locations that other calculations on the sheet will work from. Any ideas?

[sunny_talwar · Accepted solution]

Try this expression

=Sum({1<Type={'A'},Location = $::Location>}Items)

[UncleRiotous]

Thanks, that works great.  Could you explain what it's doing though as I need to translate the solution back into my main dashboard.

[sunny_talwar]

It is doing 3 things within set analysis

1) Show only those rows where Type = 'A'

Type={'A'}

2) Show only those rows which corresponds to your (direct) selection in Location field

Location = $::Location

3) Ignore (direct or indirect) selection or filter based on any other field in the dashboard

{1<