Group sum excluding a value

Is there a way to get a group sum but to exclude certain values such as 0 or null?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi  Steven Bohlson , you can achieve this by creating a group_sum formula and including filters 'column_name != {null} column_name != 0' in the search bar along with the formula. This will calculate the group sum after applying the filters on the columns. 
    Alternatively, if you want to remove certain values from all the results derived from a worksheet, you can create an aggregate worksheet after selecting the required columns and applying filters 'column_name != {null} column_name != 0' to it. Any searches performed on this aggregated Worksheet will exclude the above values. 

  • Nikhita Chandra Thank you. That did answer the question I asked. I should have been a bit more specific. I am taking a group of sales people and finding their average sales on a per month basis. I would also like to take an average of the sales people who do better than the average and an average of the people who do less than the average and show all three averages on the same graph.

    I have manged to get the overall average graphed and I have created two more columns, one with the sales figure for people above average and the rest listed as null and one with the sales numbers for people lower than average and the rest nulls. I am trying to work out a way to get the averages of these columns as well. If I use an overall filter to exclude the nulls then I basically lose a column.

    I get the feeling I'm going about this the wrong way.

  • Steven Bohlson Thank you for the clarification. The average of the 'above average' and 'below average' can be viewed in the KPI metric available at the end of our search result (the result will take care of nulls which was your concern). The aggregation on it can be changed to average as shown in the first screenshot. This KPI can also be pinned to the Pinboard by clicking on the pin that appears on hovering over it.

    If you want to view the result as a separate column-

    ThoughtSpot does not allow aggregate formulae to applied on other aggregate formulae. Assuming 'Group A', 'Group B', 'Average Sale per Customer' and 'Group Average' are aggregate formulae, you will need to create an aggregated worksheet from the parent Worksheet and create the 'average of above average' and 'average of below average' formulae on top of it. (Please refer Images 2, 3, 4 and 5)

    Like 1
  • Thank you for the detailed response. It was very helpful!

Like Follow
  • Status Answered
  • 2 yrs agoLast active
  • 4Replies
  • 804Views
  • 2 Following