Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

siddharth_s2
New Contributor III

Limiting a dimension

Hi,

How do I limit a dimension so that it only shows the values if it satisfies a criteria.

For ex, say I have a location dimension and want to plot a graph with it as dimension and SUM(Profit) as my measure.

However, I only want to show those locations where SUM(Profit) > 1000 on the graph, how do I do that?

Thanks in advance.

Tags (1)
7 Replies
siddharth_s2
New Contributor III

Re: Limiting a dimension

I tried this:

IF(SUM(Surveys)>1,

aggr(if(rank(SUM(Surveys))<=5,[Assignment Group],'Other'),[Assignment Group]),0)

However it says invalid although there is no err in calculation.

siddharth_s2
New Contributor III

Re: Limiting a dimension

Made some advancements:

This is what I tried last:

IF(SUM(Surveys)>1,

aggr(if(rank(-SUM([NPS Score])/SUM(Surveys))<=5,

[Assignment Group],'Other'),[Assignment Group]),NULL())

I am trying to find the bottom 5 however on a measure to which I have a cut off of SUM(Surveys) > 1

When I try the above, it is giving an "Invalid dimension" error.

Thanks in advance.

passionate
Valued Contributor

Re: Limiting a dimension

Hi Sidharth,

Try if(Sum(Profit)>1000,Sum(Profit)) in definition of your chart and then use dimension limit option.

Regards,

Pankaj

Not applicable

Re: Limiting a dimension

Hi

In QlikView 12 you can simply use "Dimension Limits" tab to manage displayed values.

MVP
MVP

Re: Limiting a dimension

Hi Siddharth,

Try like this using Set Analysis, using If() and Aggr() sometimes will have performance issues.


Sum( {$<Location = {“=Sum(Profit ) > 1000”}>} Profit )


Hope this helps you.


Regards,

Jagan.

siddharth_s2
New Contributor III

Re: Limiting a dimension

Thanks Pankaj, Meisam and Jagan but I am not only trying to limit the dimension, I have to sort them as well which is why I have to use rank.

IF(SUM(Surveys)>1,

aggr(if(rank(-SUM([NPS Score])/SUM(Surveys))<=5,

[Assignment Group],'Other'),[Assignment Group]),NULL())

The above formula first limits the dimension to Surveys>1 and then ranks them with a negative number so that I get the bottom 5.
Since for my logic, the bottom 5 must not consider the groups which have less than 1 survey, I limit them.

Once I use this as my sorting expression,

IF(SUM(Surveys)>1,

aggr(if(rank(-(SUM([NPS Score])/SUM(Surveys)))<=5,

-(SUM([NPS Score])/SUM(Surveys)),-99999),[Assignment Group]),NULL())

Once this is sorted, I get the bottom 5 groups which have a bad score.

However this is giving an invalid dimension error

@meisam, I am using Qlik Sense and not Qlikview.

Regards,

SS.

OmarBenSalem
Esteemed Contributor

Re: Limiting a dimension

The simplest way to do this :

On your dimension; check "LIMIT DIMENSION"

Capture.PNG

It will be calculated using your measure:

Capture.PNG

result :

Capture.PNG

Community Browser