How the FirstNode() function works in TIBCO Spotfire

How the FirstNode() function works in TIBCO Spotfire

book

Article ID: KB0077027

calendar_today

Updated On:

Products Versions
Spotfire Analyst All Versions

Description

There are scenarios when you may want to include/exclude the first bar/line value from the calculation, in such cases you can use FirstNode function. FirstNode function basically shows the first value of the bar/line node.

Suppose the scenario is you want to show cumulative Sales excluding the first month (node) in every year. So let's see how FirstNode would work for this use case:
  • Below are the total sales per month per year:
User-added image
  • Below is the first node value showed using custom expression 
     Sum([Sales]) OVER (Intersect(Firstnode([Axis.X])))
    User-added image
  • Similarly to exclude the first node from cumulative sum, you can have expression like 
     Sum([Sales]) OVER (AllPrevious([Axis.X])) - Sum([Sales]) OVER (FirstNode([Axis.X]))
User-added image
Note the above expression would not work in cases when Month or Quarter are present on X-Axis through BinByDateTime expression, even though you try evaluating X-Axis on current filtering only. This is because BinByDateTime for Month, Quarter, etc fills out all known categories, regardless of whether there is data or not, hence evaluating on current filtering only does not really apply in those cases and blank visualization like below would be seen.
User-added image
To resolve this issue, use Month([Date]) or Quarter([Date]). If multiple levels need to be used, you can either create a hierarchy and use that hierarchy in the X-Axis or use expression like this <Year([Date]) NEST Month([Date])>
  • Now in case of multiple hierarchy levels, if you try to show the first node value per year with custom expression, you can get the following result:
     Sum([Sales]) OVER (Intersect(Firstnode([Axis.X]),Parent([Axis.X])))
    User-added image
The reason for this is that the intersection happens after the first node is extracted and not prior to that as it works in AllNext() and AllPrevious() functions.

This demonstrates how FirstNode() is expected to work with current hierarchy level only on X-Axis i.e. either Year or Month only but when combining results of FirstNode() with other Over functions, it may not work in the expected way 

Issue/Introduction

This article shows how the FirstNode() function works in TIBCO Spotfire

Resolution

In order to achieve the use case described above (exclude the first node value per year from the cumulative sum of sales) you can use the following approach when you have an unsorted data set:

1. Create a column with a year-month concatenation
 case  when [MONTH] in (10, 11, 12) then Concatenate([YEAR],[MONTH]) else Concatenate([YEAR],0,[MONTH]) end

2. In value axis custom expression of your visualization, you can rank by this period column and exclude values whose rank is 1 (i.e. the first node of Axis.X) and then sum up all values per year:
 Sum(case  when DenseRank([Period],"asc",[YEAR])>1 then [Sales] else 0 end) OVER (Intersect(AllPrevious([Axis.X]),NavigatePeriod([Axis.X],"Year",0,0)))
User-added image

You can also use a different approach with the First() function if you know your data set is always going be in ascending/sorted order:

1. You can also extract value of first node using First function like
 Sum([Sales]) then First([Value]) OVER (Intersect(All([Axis.X])))

2. Similarly, you can exclude the first node value from the cumulative sum of each year by simply subtracting the first value from the total cumulative sum:
 Sum([Sales]) then Sum([Value]) OVER (Intersect(AllPrevious([Axis.X]),Parent([Axis.X]))) - first([Value]) OVER (Intersect(All([Axis.X]),Parent([Axis.X])))

Attached is the FirstNode.dxp showing all the above approaches.

Additional Information

Doc: Over Functions Doc: Over Functions in Custom Expressions Doc: Advanced Custom Expressions Doc: Aggregations in Expressions Doc: Axes in Expressions

Attachments

How the FirstNode() function works in TIBCO Spotfire get_app