Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

RegionAreaItem#Authorized HoursScheduled HoursActual Hours
TXA11200
TXA2554
TXA3022
TXB4033
TXB5040
TXC6600
TXC7704
TXC8805

The aggregated straight table that results appears like this:

RegionAreaAuthorized HoursScheduled HoursActual Hours
TXA1776
TXB073
TXC2109

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:

RegionAreaAuthorized HoursScheduled HoursActual Hours
TXB073

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.

1 Solution

Accepted Solutions
effinty2112
Master
Master

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

View solution in original post

6 Replies
swuehl
MVP
MVP

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'

Not applicable
Author

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
MVP
MVP

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
Master
Master

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

Not applicable
Author

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
Master
Master

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