topic Re: Pivot table showing on Calculated Condition where one or more dimension filters are selected in QlikView

Pivot table showing on Calculated Condition where one or more dimension filters are selected

Mon, 23 Nov 2015 15:45:00 GMT

I've a pivot table in which I only want to show the data where a user selects one or more filter value from a number of List boxes.

For example the table shows all data relating to outstanding work orders, but due to volume we want a condition met before displaying the data.

I user can select from any one of the following list boxes (used to filter the data in the table):

Month-Year Requested

Engineer

Building

Status

etc

A user could select one of more filter values, i.e. You may select 2 engineers or 1 engineer and 1 building or just 1 engineer, before the pivot table will display any data.

So the calculation condition should be true if one or more values is selected from one or more dimension and be false if no values are selected at all.

I've done this several times using only one dimension in the calculation condition i.e. "count(distinct Engineer) <>0 "

But I'm not sure if you can base this on multiple dimension at same time ?

Any ideas?

Re: Pivot table showing on Calculated Condition where one or more dimension filters are selected

Mon, 23 Nov 2015 15:49:41 GMT

Hi Dan

yes you can test

count(distinct fieldA) > 1 or count(distinct fieldB) > 1 ....

but it can make it long and tricky

may be you can set up a variable like

n = count(distinct fieldA) + count(distinct fieldB) + ...

and test only if $(n) > 0

best regards

chris

Re: Pivot table showing on Calculated Condition where one or more dimension filters are selected

Mark_Little — Mon, 23 Nov 2015 15:54:54 GMT

You could try something like

GetSelectedCount(Field1) + GetSelectedCount(Field2) > 0

When I restrict something like this i would normally go off a count of returned rows > x, then change the error message to 'Too many row returns please make selections on x,y,z'

Mark

Re: Pivot table showing on Calculated Condition where one or more dimension filters are selected

Mon, 23 Nov 2015 16:07:09 GMT

used your first suggestion, wasn't sure about the returned rows count !!!

Thanks