topic Re: Re: Hide excluded gray data in list box? in QlikView

Hide excluded gray data in list box?

oliviatww — Sun, 05 Oct 2014 21:41:38 GMT

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.

Re: Hide excluded gray data in list box?

Anonymous — Sun, 05 Oct 2014 21:54:48 GMT

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.

Re: Hide excluded gray data in list box?

Sun, 05 Oct 2014 21:55:20 GMT

Hi,

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

Re: Hide excluded gray data in list box?

oliviatww — Sun, 05 Oct 2014 22:10:44 GMT

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

Re: Re: Hide excluded gray data in list box?

Anonymous — Sun, 05 Oct 2014 22:20:18 GMT

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.

Re: Hide excluded gray data in list box?

oliviatww — Sun, 05 Oct 2014 22:34:08 GMT

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

Re: Hide excluded gray data in list box?

Anonymous — Sun, 05 Oct 2014 22:42:21 GMT

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.

Re: Hide excluded gray data in list box?

MarcoWedel — Sun, 05 Oct 2014 23:07:20 GMT

=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

Re: Hide excluded gray data in list box?

oliviatww — Sun, 05 Oct 2014 23:14:56 GMT

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.

Re: Re: Hide excluded gray data in list box?

Anonymous — Mon, 06 Oct 2014 07:14:02 GMT

You can use the following expression in the products listbox:

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

Re: Hide excluded gray data in list box?

harsh44_bhatia — Wed, 08 Jan 2020 15:56:11 GMT

Lets say we have a field called [%Measure_Label] and another flag field [%Measure_External_Flag] which has values 1,0 and works in Tandem with the %Measure_Label.

Based on Internal External Flag we would show the list values to the user.

if the user is Internal who should see all fields the set analysis variable vInternal_External_Selection should evaluate to *

and if user is External who should see fields with flag value 1 the set analysis variable vInternal_External_Selection should evaluate to 1

=aggr(only({1<[%Measure_External_Flag]={$(vInternal_External_Selection)}>}[%Measure_Label]),[%Measure_Label])