How the FirstNode() function works in TIBCO Spotfire

Date Posted: 2019-10-08
Product: TIBCO Spotfire®

Problem:

Solution:

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:

Below is the first node value showed using custom expression
```
Sum([Sales]) OVER (Intersect(Firstnode([Axis.X])))
```
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]))
```

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])))
```

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 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)))

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. Doc: Over Functions