Aggregation of Grouping Functions
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|
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?
Have a look at the following link. I believe you need to wrap the group_aggregate in a sum function. I.e.
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)?
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.