topic Re: Top 10 Rank and Dimension Limits? in QlikView

Top 10 Rank and Dimension Limits?

alexis — Tue, 09 Oct 2012 22:32:56 GMT

Let's suppose that I have a measure called Orders.Diff and a dimension Client

I wish to create a bar graph (and a straight table) that meets the following requirements:

a) Top N Clients by sum(Orders.Diff) where N is stored in a variable vTopDiffs

b) sum(Orders.Diff) < vDiffThreshold

The requirement in a) was easily achieved with:

Calculated Dimension: =aggr(if(rank(sum(Orders.Diff)) <=vTopDiffs, Client), Client)

Expression: = sum(Orders.Diff)

Any ideas how to achieve b)

Simply replacing

= sum(Orders.Diff)

with

= if(sum(Orders.Diff) < vDiffThreshold, sum(Orders.Diff))

does not work. I suspect the above needs to be added to the Calculated dimension but I have no idea how to do that. Any ideas?

Thanks in advance

Alexis

swuehl — Tue, 09 Oct 2012 22:53:00 GMT

Maybe like

=aggr(if(rank( if(sum(Orders.Diff) < vDiffThreshold, sum(Orders.Diff)) ) <=vTopDiffs, Client), Client)

If using QV11, you could also look into dimension limits:

Use as first expression something like

=sum({<Client={"=sum(Orders.Diff)<vDiffThreshold"}>}Orders.Diff)

as dimension just Clients and for this dimension, enable 'Restrict which values ...' with option 'show only' with 'Largest' vTopDiffs Value

on dimension limits tab.

Hope this helps,

Stefan

alexis — Tue, 09 Oct 2012 23:17:00 GMT

Thanx Stefan,

BOTH suggestions works perfectly so THANKS!

Can you explain to me the set analysis statement:

=sum({<Client={"=sum(Orders.Diff)<vDiffThreshold"}>}Orders.Diff)

Thank you once again

Alexis

swuehl — Tue, 09 Oct 2012 23:27:51 GMT

It's a set expression with an advanced search in the set modifier (please check also the somewhat short explanations and sample in the Help file).

Search all Clients where sum(Order.Diff)<vDiffThreshold.

You can use a similar search expression e.g. in the search bar of a list box (with field Client) to filter the Clients with low sum(Order.Diff).

Hope this helps,

Stefan