Show all values over X in one bar of bar chart

[JennE]

I have a bar chart that uses the following formula:

=dual(replace(replace(class([TripMiles],MilesBinWidth,'-'),'<=',''),'<',''),class([TripMiles],nMilesBinWidth))

Most of the values in my data are clustered at the beginning of the bar chart, with a long tail to the right where each bin may have only one or two units counted.  Is there any way to consolidate these upper values into one bar?  For example, all 15 of the trips that are over 200 miles are consolidated into one bar with the label >200, instead of each having its own bar?  It would make the chart more useful if so much scrolling to the right was not needed to see all of the data.

Thank you, 

JennE

[stevedark · Accepted solution]

Hi @JennE 

I would suggest creating the mileage bucket in the load script, rather than using the Class function. This will be much more efficient and will allow you to achieve what you are after.

let nMilesBinWidth = 10;

LOAD
   TripMiles,
   if(TripMiles >= 200, dual('>= 200', 200),
      Dual(Floor(TripMiles, $(nMilesBinWidth)) & ' - ' &
      	num(Floor(TripMiles, $(nMilesBinWidth)) + $(nMilesBinWidth) - 0.01, '0.00'),
        Floor(TripMiles, $(nMilesBinWidth)))) as [TripMiles $(nMilesBinWidth) Capped],
	Floor(TripMiles, $(nMilesBinWidth)) as [TripMiles $(nMilesBinWidth)]
    ;
LOAD IterNo() as TripMiles WHILE IterNo() <= 250;
LOAD 1 as A AUTOGENERATE(1);

Whilst this is more efficient that calculating buckets in the front end, it does have the disadvantage that the bucket size can't be edited in the front end. You should be able to use the same expression as a calculated dimension if that is important to you.

I've attached an example of the load script working.

Hope that helps,

Steve

https://www.quickintelligence.co.uk/blog/

[JennE]

Thanks @stevedark ! This works exactly the way I wanted it to.

JennE