Search code examples
spotfire

Spotfire combine data below certain count to new categorie

So I have an Excel sheet with a column that contains names and a second column that is a count, how often this name occurs (already done) so my table looks something like this:

  Name    Name CNT
  dog     24
  cat     10
  rabbit  3
  mouse   1
  ...

My problem is that there are many more values and a lot of them are equal to one, which messes up my pie chart.

Is there a way in Spotfire to combine the ones that are equal to 1 to a new category and the ones between 2 and 10 to another category and so on.

I tried using a custom expression but I can't get it to work.

Thanks for your help in advance.

Solution

  • I was able to create the below table:

    enter image description here

    Following these steps:

    1. Create "Binned CNT" binned column with the following formula: BinBySpecificLimits([CNT],1,10,50)
    2. Create a calculated column with the following formula: case [Binned CNT] when "x ≤ 1" then "Less Than 1" when "1 < x ≤ 10" then "Less Than 10" when "10 < x ≤ 50" then "Less Than 50" end
    3. Use those columns to build the below pie chart:

    enter image description here