topic Re: Use a Button to filter a Straight Table based on an aggregated expression in QlikView

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

Mon, 13 Jun 2016 21:08:34 GMT

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.

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

swuehl — Mon, 13 Jun 2016 21:35:40 GMT

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'

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

Mon, 13 Jun 2016 21:50:50 GMT

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.

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

swuehl — Mon, 13 Jun 2016 22:27:20 GMT

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.

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

effinty2112 — Wed, 15 Jun 2016 07:14:14 GMT

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

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

Wed, 15 Jun 2016 15:13:34 GMT

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!

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

effinty2112 — Wed, 15 Jun 2016 15:31:45 GMT

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