Formulas Forum

Share some of your common, but useful, business formulas that you use in ThoughtSpot.

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • I'll get things rolling with a quick formula tip. If you want to do date comparisons to the current date (which is quite common) but you don't care about the time then use today() rather than now()!

    today() holds just today's date and is cached in memory. now() holds the exact time and as such is evaluated every time it's called. As you can imagine if you've got multiple calls to now() in a formula, over millions of rows of data the overhead can add up.

    Reply Like 1
  • I'm trying to decide if I should aggregate conditional metrics on worksheets. For example if I have a conditional sales metric coding syntax as:

    "if(...)then x else y"

    -or-

    "sum(if(...)then x else y". 

    Coding as if(...) the x else y allows users to pick aggregation when searching answers (can switch from total to avg,etc). However would require training users to define aggregation if creating a new formula on the answer (ie "sum(sales)"). 

    Coding as sum(if...)then x else y would allow users to build formulas with object names vs aggregating objects in formulas on answers - simply create new formula "sales/100". I feel like this would be more intuitive to end users. 

    Open to recommendations or feedback on best practices. 

    Reply Like
  • Steph Fisher Sorry for the delayed reply here. Wanted to close the loop.

    There is not a strong argument in favor of either in terms of best practice, so if the business end user case leans on any one side particularly -- go for that one.

    Option 1 has an advantage that even though the formula for enduser looks to be not that intuitive, it would be consistent with every other fields that the enduser encounters, so they can translate the learning into threading a coherent logic for it. Option 1 gives consistency and often times for endusers trying a new tool, consistency rules over idiosyncrasy. (Think about it --  Excel has many idiosyncrasies where formulas really shouldn't be done that way, but people have learnt it and consider it the 'new normal')

    But there are perfectly valid cases, where Option 2 will give you consistency, provided every other metric is consistent in that way to the end user!

    Reply Like
  • I am very new to ThoughtSpot and have a quick question about a formula I have been working on for aging categories.  Below is the formula:

    if ( diff_days ( todays date , created date ) >= 31 and diff_days ( todays date , created date ) <= 45 ) then "31-45 days aged" elseif ( diff_days ( todays date , created date ) >= 46 and diff_days ( todays date , created date ) <= 60 ) then "46-60 days aged" elseif ( diff_days ( todays date , created date ) >= 61 and diff_days ( todays date , created date ) <= 90 ) then "61-90 days aged" else "90+ days aged" 

    As you can see it is nothing to complicated but the issue I am running into is with capitalization of the category names.  For some reason I cannot capitalize the category names.  For example you will see that all categories have lower case names, "31-45 days aged".  If I try to capitalize the Days Aged portion the formula will not recognize the input and I cannot save the formula.

    Any known issues that would cause this?

    Reply Like
  • Hi Aaron Davis , yes this is a known issue. Text values from formulas will be lowercased.

    Reply Like
reply to topic
Like Follow
  • 5 mths agoLast active
  • 5Replies
  • 894Views
  • 7 Following