topic Limit Dimensions by Calculated Expression in QlikView

Limit Dimensions by Calculated Expression

Wed, 05 Oct 2011 15:39:25 GMT

I am having trouble limiting the values that are returned based on the expression values. I have a table with supplier's line level spend data, and I have a chart that list out the suppliers and their total spend. I want to limit the chart to show only those suppliers with greater than or equal to 50K in spend. I know how to limit the number of suppliers that show using the 'Max visible Number', but is there a way to limit the dimensions based on the sum amount in the expression? I'm sure this is relatively basic, but I just can't seem to figure out the solution at this point. Any help would be greatly appreciated.

Limit Dimensions by Calculated Expression

Wed, 05 Oct 2011 15:52:46 GMT

Try something like this:

if (sum_of_expression < 50000, 'Less than 50k',

if (sum_of_expression >= 50000, 'More or equal 50k','no data')

) as total_spend_grouped

By selecting one of two options 'Less than 50k' or 'More or equal 50k' you will get limited number of suppliers.

Tom

Limit Dimensions by Calculated Expression

Wed, 05 Oct 2011 16:20:50 GMT

That works from a SQL perspective when brining in the data, but I need to limit the data within the chart itself and allow the sum of spend to be dynamic based on their selections in other fields; for example region.

So right now I have a simple straight table with the dimension "Supplier Name", and the expression of '=SUM(LINE_SPEND_AMOUNT)'. There are other fields like region, department, etc... that I want the user to be able to limit the data just like normal table. I just want the chart to limit the supplier names and spend returned to only those with spend greater than or equal to 50K. It's theoretically the same as using the Max Visible Number, but I don't want to limit based on a number, but the condition of the expression.

I did try using something similar to what you posted above when I brought the data in, but the sum of the spend amount needs to dynamically change based on other selections. Thanks for the response, it's just not exactly what I'm looking for.

Limit Dimensions by Calculated Expression

Wed, 05 Oct 2011 17:20:45 GMT

Something like this as your calculated dimension might do what you're looking for:

=aggr(if(SUM(LINE_SPEND_AMOUNT)>50000, [Supplier Name]),[Supplier Name])

Limit Dimensions by Calculated Expression

Wed, 05 Oct 2011 21:42:41 GMT

That worked great. Thank you.