topic Re: Limit the data in a table based on Input Box in QlikView

Limit the data in a table based on Input Box

Thu, 12 Sep 2013 10:41:38 GMT

Hi All,

I want to Limit the data in a table based on Input Box.

Accounts With Revenue of >5M,>1M,<1M and all accounts has to be displayed when selecting All Accounts.

Please help me to achive this using Dimension Limits or Using Set Analysis.

Thanks for your help in advance.

Re: Limit the data in a table based on Input Box

swuehl — Thu, 12 Sep 2013 12:09:50 GMT

I assume you are using a table diagram with Account as dimension and something like =sum(Revenue) as expression.

You can try something like this as expression:

=pick(match( vAccountSize, 'Accounts With >5M Revenue','Accounts With > 1M Revenue','Accounts With < 1M Revenue','All Accounts'),

sum({<Account = {"=sum(Revenue)>5000000"}>} Revenue),

sum({<Account = {"=sum(Revenue)>1000000"}>} Revenue),

sum({<Account = {"=sum(Revenue)<1000000"}>} Revenue),

sum( Revenue)

)

Re: Limit the data in a table based on Input Box

Thu, 12 Sep 2013 14:14:51 GMT

Thanks Swuehl.

I have around 10 tables to be filtered when i apply the filter. Most of the tables dont show revenue data.

Show some other measures like Headcount,utilization of resources.Help me on this.

Is this possible through Dimension Limits in QV 11.Please advise.

Re: Limit the data in a table based on Input Box

swuehl — Thu, 12 Sep 2013 14:24:02 GMT

The real important part is the set analysis part:

{<Account = {"=sum(Revenue)>5000000"}>}

which will filter the Account values according their revenue.

So you don't actually need to sum Revenue in your chart expression, just use set analysis in all your expressions.

When counting heads for example:

=count({<Account = {"=sum(Revenue)>5000000"}>} Heads)

This will filter the accounts based on Revenue > 5M, then counting Heads (probably not the way you do it in your chart, just as an example, you'll need to get the idea.

As far as I see, dimension limits is probably not the way to go here.

Re: Limit the data in a table based on Input Box

Thu, 12 Sep 2013 14:33:30 GMT

Hi,

You can also try with the if loop

=IF( vAccountSize= 'Accounts With >5M Revenue',sum({<Account = {"=sum(Revenue)>5000000"}>} Revenue), IF(vAccountSize='Accounts With > 1M Revenue',sum({<Account = {"=sum(Revenue)>1000000"}>} Revenue), IF(vAccountSize='Accounts With < 1M Revenue',sum({<Account = {"=sum(Revenue)<1000000"}>} Revenue), IF(vAccountSize='All Accounts',sum( Revenue)))))