Aggregation of Grouping Functions

Hi All,

I have a table with multiple line items for a unique document (one for each month), each showing different sales amounts:

Doc Date Amount Total Doc Amount
123 Jan 20 10 40
123 Feb 20 20 40
123 Mrz 20 10 40
456 Jan 20 0 0
456 Feb 20 0 0

I want to calculate "Total Doc Amount" in ThoughtSpot and tried group_sum as well as group_aggregate, both in an answer and on a worksheet level.

My problem is - it doesn't aggregate properly. For the above example, I'd expect ThoughtSpot to just show "40"  if I took out "Doc", "Date" and "Amount". But instead it shows "40", "40", "40", "0", "0".

Is there a way to get around this?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Have a look at the following link. I believe you need to wrap the group_aggregate in a sum function. I.e.


    Like 1
  • Hi Damian, thanks! This already helps a lot!

    Based on the Total Doc Amount I now want to flag the Documents with either "yes" or "no". Unfortunately the best practice article states: Do not use with conditional operators. 

    I tried the examples below, but this doesn't really help.

    Is there a way to create a new attribute with the two values "yes and "no" depending on the Total Doc Amount (if 0 then no)?

  • Hi,

    With group aggregate you can create the flag, however you can't re-aggregate a string value. I.e. you will get a row for each document rather than a row for Yes and a row for No. The solution to this is to:

    1. create a search with the group aggregate total

    2. the search should include a key you can join back to the data model

    3. save the search as a view 

    4. join the view back to the base tables 

    5. add a new calculated field that returns a yes/no based upon the aggregate column from the view

    Note to join a view to the base tables a --adv flag must be enabled. Please reach through support portal and/or your CSM to get this information.

  • Hi Christoph Meier ,

    What about adding the yes or no flag to the table as part of the ETL. You can create a new dimension table with a document id column and the "yes or no" attribute column. Then you can connect the new table back onto the facts table with the document id.



Like Follow
  • 1 mth agoLast active
  • 4Replies
  • 36Views
  • 3 Following