Selectable Straight Table Chart

[agsearle]

I have created a straight table chart, with a calculated expression to show when columns A and B match, for example:

1 2 3
AAAA BBBB MATCH
AAAA CCCC NO MATCH
AAAA BBBB MATCH

My problem is, I want to be able to select ALL the MATCH items, so it restricts the views of my other sheet objects. The source data is more complicated than this simple example and is from more than one table, so I'm not sure this can be done in the script?

How can I restrict this in the chart?

[#2]

The expression which reflects match or NOT MATCH could be changed to

SUM(IF(xxxxx = 'MATCH',1,0)) And you'll only see the matching fields in your table.

[agsearle]

That came up with 'error in expression', but I managed to add a calculated dimension, that is searchable, which works fine.

However, is there any way to lock the selection now? When you clear the selection you lose the MATCH items.

[msteedle]

I believe you would have to perform the lock on list boxes for each of the individual fields, [1] and [2] in your example.

If this is a common need, you could build a macro to automatically perform the selection and locks, or else it would really be best done in the script, storing a flag of some type for instances of [1] and [2] matching.

[agsearle]

The example is a very simplistic view of what data I have.

Unfortunately columns 1 and 2 have numbers in them, that are all different, so locking the columns wouldn't work. Here a few rows from my actual table:

BP 13 CA 13 BP M 13 CA M 13 Matched? Time Slice From M 13 Time Slice Expires 13
0010192207 4015232000 0010192207 004034914278 MATCH 19/10/2009 19/10/2009
0010411235 4000143167 0010411235 004025802591 MATCH 14/11/2006 14/11/2006
0010472227 4012422602 0010472227 004034717014 MATCH 06/09/2009 06/09/2009
0010546502 4000209808 0010546502 004035021354 MATCH 04/10/2009 04/10/2009
0010658169 4009150156 0010658169 004033158418 MATCH 29/04/2009 29/04/2009

The formula for the 'Matched?' column is:
=IF(Date_Time_Slice_From_M_13=Date_Time_Slice_Expires_13 AND BP_13=BP_M_13, 'MATCH', 'NO MATCH')

I only want to see the 'MATCH' ones, but I can't seem to lock them.

Also the data used in the formula is from 3 separate tables, so I'm no sure if it could be done in the script?

[#6]

try to replace the formula by:

=SUM(IF(Date_Time_Slice_From_M_13=Date_Time_Slice_Expires_13 AND BP_13=BP_M_13, 1, 0) In this case you will always see the MATCH data whatever the filter is

[agsearle]

I assume there's a close bracket at the end of the formula, but this still says '// Error in calculated dimension'.

[agsearle]

By entering this formula as a calculated expression, rather than a calculated dimension, it doesn't have the error anymore, however it still shows all entries with either a 1 or a 0. That was the reason for using a calculated dimension as you can make the column searchable.

Is there any way to amend this to just show the 1's in the table, exlcuding all the 0's from the result, without the need for the above?

[msteedle]

The default setting for charts is not to show rows with zero values from expressions, so I'm surprised it's working as you describe. The setting is on the Presentation tab, "Suppress Zero-Values".

You could also omit the third parameter of your IF statement, which is the equivalent of using a null() there. Then the non-matching rows shouldn't appear in the resulting table.

If that doesn't work, you may just need to attach an application with a few sample rows and the table you are currently trying to use.

[msteedle]

I forgot to mention - you could also make your existing expression with the 1s and 0s searchable (assuming this is a straight table), then just search for 1s and hit enter to capture all of them.