Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Fabien
Contributor III
Contributor III

Custom Range Bucket

Hi all,

 

Hoping you can help with the below:

I'm trying to create some customized ranges, 

Untitled.png

 

However it is currently aggregating the Sum(Sales) into the one value and disregards the ranges:

 

Untitled.png

 

if(Aggr(SUM(Sales),Country) > $(vRangeMax),'>'&$(vRangeMax),

if(Aggr(SUM(Sales),Country) < $(vRangeMin),'<'&$(vRangeMin),

if(Aggr(SUM(Sales),Country) > $(vRangeMin) and Aggr(SUM(Sales),Country) < $(vRangeMax),$(vRangeMin)&' - '&$(vRangeMax))))

Labels (1)
1 Solution

Accepted Solutions
Fabien
Contributor III
Contributor III
Author

I was able to find a solution by creating a Dimension instead of a Measure:

if(Sales >=$(vRangeMax),'> '&$(vRangeMax),
if(Sales <= $(vRangeMin),'< '&$(vRangeMin),'> '&$(vRangeMin)&' < '&$(vRangeMax)))

 

View solution in original post

7 Replies
rubenmarin

Hi, if each country sales is distributed in ranges it means that the sales are being grouped by something below country, it is by order? by customer? by product?

Add that field to the aggr to split the value, like:

if(Aggr(SUM(Sales),Country,Product) > $(vRangeMax),'>'&$(vRangeMax),...

Fabien
Contributor III
Contributor III
Author

Thanks but I have tried that before and it just shows a Null value...

rubenmarin

Yes, in that case also the if should be inside the aggr:

Aggr(SUM(Sales)> $(vRangeMax),'>'&$(vRangeMax),...,Country,Product) 

Fabien
Contributor III
Contributor III
Author

That did not work either unfortunately...

I have attached a sample of the data for reference.

vinieme12
Champion III
Champion III

Try below

=AGGR( if( SUM(Sales) > $(vRangeMax) ,'>'&$(vRangeMax)

,if( SUM(Sales) < $(vRangeMin),'<'&$(vRangeMin),'>'&$(vRangeMin) & '<'&$(vRangeMax) ) ) ,Country,Product)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Fabien
Contributor III
Contributor III
Author

I was able to find a solution by creating a Dimension instead of a Measure:

if(Sales >=$(vRangeMax),'> '&$(vRangeMax),
if(Sales <= $(vRangeMin),'< '&$(vRangeMin),'> '&$(vRangeMin)&' < '&$(vRangeMax)))

 

rubenmarin

All this time I thought this is a dimension, it should be a dimension to split in different rows.

I'm not sure how that is working, it might work on individual Sales values, but not grouping by order, product or customer.