Use a Button to filter a Straight Table based on an aggregated expression

[#1]

Having a hard time finding a good way to do the following...  I have a raw data set that resembles the following:

Region	Area	Item#	Authorized Hours	Scheduled Hours	Actual Hours
TX	A	1	12	0	0
TX	A	2	5	5	4
TX	A	3	0	2	2
TX	B	4	0	3	3
TX	B	5	0	4	0
TX	C	6	6	0	0
TX	C	7	7	0	4
TX	C	8	8	0	5

The aggregated straight table that results appears like this:

Region	Area	Authorized Hours	Scheduled Hours	Actual Hours
TX	A	17	7	6
TX	B	0	7	3
TX	C	21	0	9

The goal is to present this information as shown in the aggregated straight table, along with a set of buttons that will allow users to show rows with a 0 in the appropriate "Hours" column.  So for example, if the button marked "Show Authorized = 0" is pressed, the above table would then display the following:

Region	Area	Authorized Hours	Scheduled Hours	Actual Hours
TX	B	0	7	3

Preferable the button would act as a toggle, allowing the full set to be shown again when pressed.

I have tried a number of options, including:

- adding a flag to the data rows at load time (doesn't work because i want see the rows that aggregate to 0, not individual 0 rows)

- creating a calculated dimension, like =if(aggr(sum([Authorized Hours], Region, Area) = 0, 1, 0), but i cannot get the button to recognize the value of the calculated dimension.

Any advice is greatly appreciated.

[swuehl]

Have you tried something like this:

Create your button with an action Selection - Select in field

field name:

Area

search string:

='=Sum([Authorized Hours])=0'

[#3]

I have, but I would need it to filter on the combination of the Region and Area fields.  Just using Area does not provide the correct results.

[swuehl]

Then I would suggest that you create a key in your script made of Region and Area and use this as field to filter in your Select in field action.

[effinty2112 · Accepted solution]

Hi David,

This script will give you the flags you need.

Data:

Load

Autonumber(Region & '|'& Area, 'RegAreaID') as RegAreaID,

*;

LOAD * INLINE [

    Region, Area, Item#, Authorized Hours, Scheduled Hours, Actual Hours,

    TX, A, 1, 12, 0, 0,

    TX, A, 2, 5, 5, 4,

    TX, A, 3, 0, 2, 2,

    TX, B, 4, 0, 3, 3,

    TX, B, 5, 0, 4, 0,

    TX, C, 6, 6, 0, 0,

    TX, C, 7, 7, 0, 4,

    TX, C, 8, 8, 0, 5

];

FlagSumHours:

Load

RegAreaID,

Sign(Sum([Authorized Hours])) as FlagAuthorized,

Sign(Sum([Scheduled Hours])) as FlagScheduled,

Sign(Sum([Actual Hours])) as FlagActual

Resident Data Group by RegAreaID;

Left Join(Data)

LOAD * Resident FlagSumHours;

Drop Table FlagSumHours;

Kind regards

Andrew

[#6]

Thank you Andrew!

I was able to apply this logic to my model and it appears to be working!

Thanks all for your help and input!

[effinty2112]

You're very welcome Davis, glad to be of help!