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.

    sum(group_aggregate(sum(amount),query_groups()+{doc},query_filters()))

    https://docs.thoughtspot.com/6.0/reference/practice/reaggregation-scenarios.html

    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)?

    Like
  • 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.

    Like
  • 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.

    Best,

    Mike

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