Limiting a dimension

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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]

Hi Sidharth,

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

Regards,

Pankaj

[#5]

Hi

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

[jagan]

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.

[Anonymous]

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]

The simplest way to do this :

On your dimension; check "LIMIT DIMENSION"

It will be calculated using your measure:

result :