topic Re: Custom Range Bucket in App Development

Custom Range Bucket

Fabien — Thu, 10 Nov 2022 05:32:12 GMT

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

Re: Custom Range Bucket

rubenmarin — Thu, 10 Nov 2022 07:22:25 GMT

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

Re: Custom Range Bucket

Fabien — Thu, 10 Nov 2022 10:52:56 GMT

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

Re: Custom Range Bucket

rubenmarin — Thu, 10 Nov 2022 11:01:29 GMT

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

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

Re: Custom Range Bucket

Fabien — Thu, 10 Nov 2022 23:09:40 GMT

That did not work either unfortunately...

I have attached a sample of the data for reference.

Re: Custom Range Bucket

vinieme12 — Fri, 11 Nov 2022 02:47:02 GMT

Try below

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

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

Re: Custom Range Bucket

Fabien — Fri, 11 Nov 2022 05:32:31 GMT

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

Re: Custom Range Bucket

rubenmarin — Fri, 11 Nov 2022 07:07:40 GMT

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.