Limit the data in a table based on Input Box

[#1]

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.

[swuehl]

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)

)

[#3]

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.

[swuehl]

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.

[#5]

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)))))