Spotfire Custom Expression

Question

In spotfire need to extract employee name for a particular quarter, how many added and left.

like we have

Employee_nm   Quarter
Mohan             1
Rohan             1
Sohan             1
Mohan             2
Rohan             2
Joseph            2

output:- When we select Quarter 2 result should

Left    Add
Sohan   joseph

Answer 1

An IronPython script could do this with a property control setting the quarter of interest.

User selects number 1-4 from a drop down menu which is stored in the document property 'lstQuarter' and connected to the script that does the calculation.

Pseudocode for the calculation:

Access the table
Fill listA with all the names from lstQuarter - 1
Fill listB with all the names from lstQuarter
Iterate through the lists comparing each name. Those in listA but not listB have been removed, those in listB but not listA have been added.
Write this information into a string, out to excel, wherever you want it.

If you want to update a table in Spotfire with this information you'll need to write it to Excel first and then replace the data table with it but this can all be scripted.

Answer 2

There is a solution to this issue that requires only a property control and 2 calculated values set into a text area plus 2 calculated columns.

Firstly, I extended the dataset to show a full year:

Employee_nm Quarter
Mohan   1
Rohan   1
Sohan   1
Mohan   2
Rohan   2
Joseph  2
Mohan   3
Joseph  3
Katelyn 3
Katelyn 4
Joseph  4
Wesley  4

Next, the calculated columns:

    Case First([Quarter]) OVER ([Employee_nm]) 
when 1 then "Q1" 
when 2 then "Q2" 
when 3 then "Q3" 
when 4 then "Q4"
end as [added]

-and-

    if(Last([Quarter]) OVER ([Employee_nm])!=Max([Quarter]),
 Case Last([Quarter]) OVER ([Employee_nm]) 
when 1 then "Q2" 
when 2 then "Q3" 
when 3 then "Q4" 
when 4 then "Q1 Following Year"
end,"") as [left]

NOTE: The above only accommodates the given dataset but can be easily be extended to accommodate multiple years.

The Property control should be set up like so:

Finally, the calculated values:

UniqueConcatenate(if([added]='${Quarter}',Concatenate([Employee_nm]," added ",[added]),""))

-and-

UniqueConcatenate(if([Left]='${Quarter}',Concatenate([Employee_nm]," left ",[Left]),""))

NOTE: The above values will exhibit a leading comma in many cases due to the 'UniqueConcatenate()' function. These can be dealt with using text functions within your calculated values and that logic has been left out of this answer in the interest of readability.

I hope this helps.

Answer 3

You can insert a calculated column to identify the employees added/removed per Quarter:

Concatenate(If(Find([Employee_nm],Concatenate([Employee_nm]) OVER
(Previous([Quarter])))=0,"Added in this Quarter"),If(Find([Employee_nm],
Concatenate([Employee_nm]) OVER (Next([Quarter])))=0,"Removed from Next
Quarter"))

You can see result per Quarter by filtering on Quarter column.