Group "Other"

Is there a way to create consolidations or custom groups based off of column elements?

For example if I'm looking at sales by country, if I have data for 192 countries plotted on a pie chart, I really want to focus on the top 10 countries and then see a group for all other countries. 

Another use case is I may want to group US, Mexico, and Canada - grouped as North America. I'll want to look at sales for North America vs all Other Countries. 

Thanks!

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Steph,
    For the first use case, you can follow these steps-
    1. Create an aggregate Worksheet using these steps - go to the search bar, select the top 10 countries and the metric, and click on 'Save as Worksheet'.
    2. Create a new Worksheet by joining the aggregated worksheet created in the previous step with the base table containing the corresponding columns. (Assuming you will perform your search on the newly created Worksheet). Be sure to join the two objects using the full Outer join in the 'Choose Sources' wizard. You can do this by doing to the data tab and clicking on 'Actions'->'Create a Worksheet'. Save the Worksheet.
    3. You can now go to the search bar, select the Worksheet created as the source and perform a search for the countries and the corresponding metric. A '{null}' will appear next to the countries that are anything below top 10 and are selected from the data corresponding to the first aggregated Worksheet. On top of this, you can create a formula to convert {null} to 'others' if you require - ifnull ("{null}", "others").
    4. Convert your search to a pie chart.
    Note - You may require to contact ThoughtSpot support at support@thoughtspot.com if you do not see your aggregated Worksheet in the 'Choose Sources' wizard in step 2.

    For the second use case, you can create a formula (either in the worksheet or in the search bar) that will show 'North America' next to US, Mexico and Canada and 'Others' next to all the other countries. A search can be done using this column instead of the countries column and the result can be converted to a pie chart. The formula will look like (assuming the column is called country) - if (country = 'US' or country = 'Mexico' or country = 'Canada' )then country else 'others'
    Let me know if you have any questions.

    Like
  • Nikhita Chandra - Thanks for the response. I'm currently running 4.2.3 but will hopefully be on 4.3 soon. Until then I can only join aggregated worksheets to other aggregated worksheets (not worksheets or tables). I will follow up once we have 4.3.

    Like
  • Hi Steph Fisher ,

    You can still contact support and they can enable the functionality for you on this version. Hope the information is useful.

    Like 1
Like Follow
  • Status Answered
  • 2 yrs agoLast active
  • 3Replies
  • 740Views
  • 2 Following