Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
abonnery
Contributor III
Contributor III

Multiple value dimension - 'Above a certain value'

Dear Qlik Users, 

 

I am trying to  create a dimension to filter a very simple table made of projects and their corresponding values: 

project range qs.PNG

I would like to create a dimension that I will use as a filter which values would be : 'Below 1M€', 'Above 1M€', 'Above 5M€', 'Above 15M€', 'Above 50M€'.

So some projects could be in several of these fields (a 7M€ project would be both in  'Above 1M€', and in 'Above 5M€'). 

When I tried creating the value, I did not see the behaviour I wanted to see; When filtering with the 'Above 1M€' value, I was only shown the projects between 1 and 5M€ but not those above, as expected.

Here is the script I have tried :

 

 

Aggr(if(sum([AED Amount])<1000000,
dual('Below 1M€',1),
if(Sum([AED Amount])>=50000000,
dual('Above 50M€ ',5),
if(Sum([AED Amount])>=15000000,
dual('Above 15M€',4),
if(Sum([AED Amount])>=5000000,
dual('Above 5M€',3),
if(Sum([AED Amount])>=1000000 ,
dual('Above 1M€',2)))))),[Project Name])

 

 

 I suppose this is a problem of attributing multiple value to a single one, and to implement a hierarchy in between the values of a dimension. 

Could you please help me creating such a dimension ?

Thank you for your help.

4 Replies
vikasmahajan

can you  elaborate more what do you mean by hierarchy ? what is a exact requirement?

 

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
anat
Master
Master

can you try similar to below 

If( sum(amount)>=0 and sum(amount)<=10 ,"Bucket1",

     IF(sum(amount) >=11 and sum(amount)<=20 ,"Bucket2",    

              IF(sum(amount) >=21 and sum(amount)<=30 ,"Bucket3")))

 

abonnery
Contributor III
Contributor III
Author

There is a hierarchy in between the values of this dimension because the project filtered as 'above 50M€' should also be in 'Above 15M€'. 

So the Hirarchy would be 'Above 1M€'< 'Above 5M€' <'Above 15M€'< 'Above 50M€'.   So some values of this new dimension would be "included" in others. 

I would like to see  all the projects which value is  "ABOVE" a certain specific value (1, 5, 15, 50). (AND NOT BELOW another)

 

abonnery
Contributor III
Contributor III
Author

Thank you for your answer. But this is not what I am trying to do. Your solution are slices of values. 

I would like to see  ALL the projects which value is  "ABOVE" a certain specific value (1, 5, 15, 50). (AND NOT BELOW another)