i have to bin the temp column and then calculate the average of loss that falls in that bin
eg:
temp bin= (2931,2941),(2941,2951)…
loss avg= -1300, -1200,…
plot this in histogram as temp bin on X-AXIS and loss avg on Y-AXIS
i have to bin the temp column and then calculate the average of loss that falls in that bin
Hello @shivam_singh, welcome to the QuickSight Community!
How many bins for the temp are you hoping to make? From my experience, there are 2 ways to do this. You can either utilize custom SQL to build out your dataset where you create groups for each bin with select statements, add a field for bin where you can create a name for each group, then use a where clause to only grab temps within that bin. Once you do that for each bin, you can use a UNION clause to join the select statements together in a single table where the bin field will determine which group they fall in to.
Alternatively, you can use an ifelse statement in a calculated field, where you create the bin field and check if the temp value fits into each of the groups you want to create.
ifelse({temp} >= 2931 AND {temp} < 2941, '1',
{temp} >= 2941 AND {temp} < 2951, '2',
etc...)
You can also try using functions like stdev, rank, or percentileRank to see if it assists in your use case. I think one of the first 2 options though is most likely to lead to your desired output. Please mark my response as the solution if you are able to get the display how you wanted, otherwise let me know if you have any follow-up questions. Thank you!
i don’t have option for SQL query.
the ifelse function will work fine
1 Like