Specifying automatic bins in bar chart

Question & Answer
, ,
1

I need to create a bar chart , on x axis i should show average demand interval. But average demand interval can vary from business to business. One company might be having average demand interval of products in 1000 range for some other it might be in millions. I created a calculated field where I’m specifying manually if else(avg demand interval>0 and <100 then ‘0-100’ something like this. But i don’t want to specify range it should calculate automatically. I have used histogram it will divide into buckets but it allows only one field in values, i need to show no. of products in that range and also some metrics values like wape. And again in histogram

image
image830×302 13.1 KB

i need range like 100-200 and as histogram doesn’t support more than one field in its value field. Is there an option which creates bins may be 5 bins based in values and the bins should be like 0-1000, 1000-2000, i don’t want decimal or number like 164 shown in histogram. Note: i need to create bar chart.

2

Hello @Awan !

Can you clarify what you mean by “calculate automatically” here:

Do you want the calculation to automatically update the ranges based on another dimension?

Ultimately, to make this a bar chart with ranges similar to a histogram but with more flexibility I think that your ifelse calculation for the x-axis will be the best option.

3

Hey @duncan suppose i have a field ‘xyz’ with different values, may be the minimum is 52 and maximum is 500. this number will change as the data changes so i cannot use the exact numbers in calculated field. I need to create bins dynamically. Like, 10-100,200-300, 300-400 and 400-500. I know the size may vary but suppose i want 4 bins based on minimum and maximum value. Let minimum 1000 and maximum 5000 now. the bins will be 1000-2000,2000-3000,3000-4000,4000-5000. and also the numbers wont be like this suppose minimum is 546 and maximum is 1222. now i need to create bins From may be 400 to 1300. something like this may be using round function.

4

Hello @Awan !

I would recommend doing this in SQL prior to ingesting into QuickSight. SQL will allow you more options for naming the field and having it be dynamic.

That way you could do something like this:

SELECT 
        xyz,
        MIN(xyz) OVER () AS min_value,
        MAX(xyz) OVER () AS max_value
    FROM your_table

And then:

CASE 
WHEN xyz >= ROUND(min_value / 100) * 100 AND xyz < ROUND((min_value + (ROUND((max_value - min_value) / 4 / 100) * 100)) / 100) * 100 THEN
CONCAT(ROUND(min_value / 100) * 100, '-', ROUND((min_value + (ROUND((max_value - min_value) / 4 / 100) * 100)) / 100) * 100)
//repeat this to create four bins...
ELSE CONCAT(ROUND((min_value + 3 * (ROUND((max_value - min_value) / 4 / 100) * 100)) / 100) * 100, '-', ROUND(max_value / 100) * 100)