0

Conditional Sum

Hi,

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

Raj

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 

      sum(if(<condition>,Schema.Table.Field,0.0))

      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