Conditional Sum


I would like to know if there is a way to do conditional sum  in incorta.  Example I want to sum all the transaction amounts (history) for the policy as of today or any day. This can be achieved visually using aggregated table but I want to use it in Formula rather than doing using Aggregated table.


thank you for the help


5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • i have accomplished this by using a conditional statement such as the  if or case in a new formula field.  Once you've set that condition in the new formula you can use an aggregate either in the formula or the aggregate table type.

    Reply Like
  • you mean  like

    sum( if (tnxdate<= policydate,sum, null)) ?

    Reply Like
    • Prudhvi Raj 


      this would be the syntax.  Keep in mind you have to use this formula in an insight that performs some sort of aggregation (bar chart, agg table, metric).  

      Reply Like
    • Prudhvi Raj Absolutely put the formula in a business schema view.  This is a best practice.  However, my point remains.  The formula, whether defined in a business schema view or in an insight, has to be used in an insight that aggregates.

      Reply Like
  • I will try that but why should this formula be in insight and not in Business schema or schema level

    Reply Like
Like Follow
  • Status Answered
  • 6 mths agoLast active
  • 5Replies
  • 64Views
  • 3 Following