Business Intelligence Tools

Spotfire Expressions — 2 ways to write expressions with OVER and node navigation

  • Would you like to learn more about the Spotfire expression language?
  • Are you having difficulty getting the right level of detail in your calculations?
  • Are you struggling with calculated columns?

In the last post, I covered OVER, intersect and node navigation methods.  Now that you are familiar with them, know that there are two ways in which expressions can be written.  As you can see in the screenshot below, both expressions will calculate a cumulative sum of OIL production for each Lease.  In the first expression, the node navigation method is referenced within the intersect.  In the second expression, the column that should be used in the intersect is specified first, followed by the node navigation method.  The result is the same.

Sum([OIL]) OVER (intersect(AllPrevious([D_DATE]),[c.Lease]))

Sum([OIL]) OVER (intersect([c.Lease],AllPrevious([D_DATE])))

 

8-15-2016 6-43-39 PM

I prefer to use the second syntax.  I feel this is easier to think through.  For example, I read this expression as — Give me the sum of oil for each Lease for all previous D_Dates.

5 thoughts on “Spotfire Expressions — 2 ways to write expressions with OVER and node navigation

  1. K Reply

    Thanks for this. This is a near incomprehensible topic and this is the best introduction I’ve found

    • Julie Schellberg of Big Mountain Analytics, LLC Post authorReply

      Thanks for the feedback. I’m glad you found it helpful!

  2. Gaurav Reply

    Thank you for the post it is helpful. I have used the over function to create a calculated column but somehow it does not work for me but just return the same value for all the rows

    The expression is as below :
    Avg(Sum([AVG_INCRE_ADJ]) / (Sum([NOT_WON_COMMITTED_ADNR]) + Sum([WON_COMMITTED_ADNR]))) over ([SVP_NAME])

    But the same value is repeated for each row irrespective of the SVP name

    Can you please help

    • Julie Sebby Post authorReply

      This will return only one number because you aren’t using any over statements…..you need something like this for each aggregation.

      Sum([Avg_INCRE_ADJ) over [some column]

      Sum([Avg_INCRE_ADJ) will sum up the column and return one number.

      Sum([Avg_INCRE_ADJ) over [some column] will sum up the values FOR EACH UNIQUE VALUE in [some column]

      Hope that helps.

  3. Julie Sebby Post authorReply

    You will need an over statement for each aggregation. You are missing an over statement in the first two parts of the expression.

Leave a Reply

Your email address will not be published. Required fields are marked *