Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
Hi Sidharth,
Try if(Sum(Profit)>1000,Sum(Profit)) in definition of your chart and then use dimension limit option.
Regards,
Pankaj
Hi
In QlikView 12 you can simply use "Dimension Limits" tab to manage displayed values.
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.
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.
The simplest way to do this :
On your dimension; check "LIMIT DIMENSION"
It will be calculated using your measure:
result :