Filter records in Chart on aggregate count of records in the same table

[#1]

I have a chart that shows are set of data filtered on a few pieces of logic.

In addition, I would like to filter so that I only keep records that meet some logic related to aggregate traits of the filtered data.

For example, when I aggregate the filtered data, I get the following results

Company     Record Count

AAA               25

BBB               100

CCC               150

DDD                50

I would like to filter the underlying data to only keep data where the aggregate record count is  <= 50.  So the underlying data only displays AAA and DDD records.

Any help would be really appreciated.

[sunny_talwar]

May be like this

If(Expression <= 50, Expression)

[vishsaggi]

What is your expression used to get this record count? Try using set analysis to get < 50. If you can share some sample data would be easy to work on.

With your current example data you can do something like

Expr: = Sum({< RecordCount = {'<=50'} >}RecordCount)

[#4]

Thank you for the responses.

I can successfully filter my aggregate table with an IF statement and suppress zero values, so I have a table that looks like this:

Company     Record Count

AAA               25

DDD                50

In actuality, it has several other Calculated Dimensions that I use to test logic, so the real table looks like this:

Company     Logic1          Logic2          Record Count

AAA                Y                    Y                       25

DDD                Y                    Y                       50

But I cannot see how to filter the original data table (with 50+) so that it only has records with AAA and DDD.   I can do it manually - if I use my mouse to highlight the Company column in the filtered aggregate table, Qlik applies the Company filter to the original data, but I cannot seem to do it programmatically.

[effinty2112]

Hi Lyon,

In script try something like:

DataTable: //Your fact table

Load

.

.

.

From ....

Inner Join(DataTable)

Load

Company

Where RecCnt <=50;

Load

Company,

Count(RecordID) as RecCnt

Resident DataTable

Group by Company;

Instead of an inner join you could use a similar script to create a mapping table then use that to add a flag to your data.

Or in the front end

You can put set analysis into all your expressions or use a calculated dimension:

Aggr(If(Count(RecordID) <=50, CompanyID),CompanyID)

Use that dimension in a chart, label it as CompanyID and set it to Suppress When Value Is Null in the Dimensions tab.

Cheers

Andrew