Hide excluded gray data in list box?

[oliviatww]

Hi there,

Wondering if that is possible to hide excluded gray data in a list box for example I have customer 1, customer 2 and customer 3 and I have different products for each customer. When I select customer 1 then I would like to show products that is related to customer 1 only and hide products that is not related to customer 1 but customer 2 and 3.

Any help is appreciated.

Thanks.

[Anonymous]

Hi,

If you use an expression for the list box you can do this, but I would not recommend doing it as it "breaks" some of QlikViews great associative functionality..

Format

aggr(only(<fieldname>), <fieldname>)

Sample:
=aggr(only(Product), Product)

Also, see the attached file. Hope this helps.

[#3]

Hi,

From the products listbox properties, you can check the option "Hide Excluded".

[oliviatww]

Thanks for all your reply.

Please look at my example apps.

For example if I select cust1 under Customer list box then it should show prod11, prod12, prod13 under Product list box but hide the rest (don't want to show the gray part or excluded data) or if select cust4 then show prod41, prod42, prod43.

Thanks

[Anonymous]

Hi Tze,

You need to do more in your data modeling part as well. You have 6 separated tables, that contains the same data type. I have concatenated them in the attached app, as well as used the aggr function to remove excluded products from the list box.

See attached file.

[oliviatww]

Thanks Kim for your reply.

Unfortunately I don't want to concatenate them, this is because actually I'm working on Financial, Sales, Purchase and Inventory instead of cust1, cust2, cust3 and cust4. I'm just using a simple example to let others understand my question..

For example,

Financial:

LOAD * INLINE

[__FinancialID, _FinancialDimension

11, Trans Amt

12, Budget

13, Variance

];

Sales:

Concatenate LOAD * INLINE

[__SalesID, _SalesDimension

11, Qty

12, Selling Price

13, Cost Price

];

Purchase

Concatenate LOAD * INLINE

[__PurchasetId, _PurchaseDimension

11, Qty

12, Purchase Price

13, Avg Cost

];

Inventory:

Concatenate LOAD * INLINE

[__InventoryId, _InventoryDimension

11, Closing Stock Qty

12, Closing Stock Value

];

ReportSelections:

LOAD * INLINE

[_ReportId, _ReportMetric

11, Financial

12, Sales

13, Purchase

14, Inventory

];

So when I select financial, then I want to show all _FinancialDimension but not _SalesDimension not _PurchaseDimension or _InventoryDimension.

Thanks

[Anonymous]

Ok. I would still have concatenated the tables into one table, adding a column saying what type of data it is. Then you can easily do your selctions in the "ReportType"-column.

Eks:

Data:

LOAD * INLINE

[ID, Dim, ReportType

11, Trans Amt, Financial

12, Budget, Financial

13, Variance, Financial

11, Qty, Purchase

12, Purchase Price, Purchase

13, Avg Cost, Purchase

11, Closing Stock Qty, Inventory

12, Closing Stock Value, Inventory

];

But, no matter how you choose to do you data modeling, it is agg(only(<DIM>), <DIM>) that is the correct way to limit you text-box.

[MarcoWedel]

=aggr(Product, Product)

should be enough, because per definition there can only be one distinct product value per distinct product value.

hope this helps

regards

Marco

[oliviatww]

Thanks again Kim for your reply.

I have another question again. Lets assume we are working on the testing.qvw that you had slightly change a bit.

When I select customer11 under Customer list box, then the Product list box showed products that are related to Customer11 which is correct. Secondly, I would like to allow user to select one of the product from Product list box but this time I would like to show both selected and excluded products. But in the testing.qvw it only should the product you selected.

Thanks.

[Anonymous]

You can use the following expression in the products listbox:

=aggr(only({<Product=>} Product), Product)