Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor III

## Custom Range Bucket

Hi all,

Hoping you can help with the below:

I'm trying to create some customized ranges,

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

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)
• ### General Question

1 Solution

Accepted Solutions
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)))

7 Replies
MVP

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),...

Contributor III
Author

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

MVP

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

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

Contributor III
Author

That did not work either unfortunately...

I have attached a sample of the data for reference.

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.
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)))

MVP

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.

Tags
Community Browser