topic Re: Smallest 3 in Dimension in QlikView

Smallest 3 in Dimension

Tue, 28 Oct 2014 18:53:42 GMT

Hello. Please see attached. I have a bar chart of items and profit margins. My dimension limit is set to show the smallest 3 values (three lowest profit margins). But only one value appears. I know it has to do with my expression which calculates a percentage (1 - (sum(Sales) / sum(Margin)); some of the sales and margin values are zero. I tried some set analyses to filter them out, but I must be overlooking something. As a result, it seems to be applying placeholders for those items for which it can't calculate a profit margin. What's the most efficient way of ignoring these items? Thanks.

Re: Smallest 3 in Dimension

its_anandrjs — Tue, 28 Oct 2014 19:04:30 GMT

For smallest 3 try Rank function some thing like below mention expression

if( Rank(1 - (Sum(Sales) / Sum(Margin))) <= 3, 1 - (Sum(Sales) / Sum(Margin)) )

Note:- And remove Dimension Limits settings.

Regards

Anand

Re: Smallest 3 in Dimension

jolivares — Tue, 28 Oct 2014 19:10:23 GMT

You just see one value, because your 3 minors include X and Z equal to zero. If you want to see in the graph go to the properties and in presentation tab unmark "Suppress Zero-Values"

Re: Smallest 3 in Dimension

Tue, 28 Oct 2014 19:19:45 GMT

Thanks. But I do want to suppress them -- the issue is I don't want them included in the ranking at all. The next two "unsuppressed" values should appear in my chart.

Re: Smallest 3 in Dimension

its_anandrjs — Tue, 28 Oct 2014 19:25:19 GMT

What are Top 3 values to be display in the chart please let me know with the values, I suggest you have to use Rank function for this with this expression.

if( Rank(1 - (Sum(Sales) / Sum(Margin))) <= 3, 1 - (Sum(Sales) / Sum(Margin)) )

Regards

Anand

Re: Smallest 3 in Dimension

jolivares — Tue, 28 Oct 2014 19:26:57 GMT

In this case you need to create an expression for that. Remember that this dimension limits works over you data as is, for this case, reject zero values and evaluates the others.

You can use the expresion above to suppress zero values.

Re: Smallest 3 in Dimension

Wed, 29 Oct 2014 15:51:01 GMT

Thanks. I've applied this to my larger (application) data set, and it returns unexpected results. It does eliminate the NULL values, but the values displayed aren't correct. I'm still investigating...

Re: Re: Smallest 3 in Dimension

Wed, 29 Oct 2014 18:59:27 GMT

See attached. That expression isn't working. I thought Rank() might take a parameter that sorted the values in descending order then took the top 5 (which would be the bottom 5), but that doesn't seem to be the case either. I simplified my example somewhat, and added more values.

Re: Smallest 3 in Dimension

maxgro — Wed, 29 Oct 2014 19:27:28 GMT

Re: Smallest 3 in Dimension

Wed, 05 Nov 2014 13:48:20 GMT

A belated thank you for this. It does work, although it doesn't handle zero values very well. So in the case that my five lowest margins are -10%, -7%, -5%, -2% and 0%, it only shows the four non-zero values. Perhaps it has something to do with supressing zero values, but unchecking it didn't quite fix it. It's a rare enough occurrence with our data set that I might just live with it.