Search code examples
spotfire

Percentage Calculation - SpotFire

I am new to Spotfire.

I have a dataset as shown below. I need to count the percentage of "late" events year wise. For instance for the year 2016 "late" event percentage is (3/14)*100.

Could anyone please say the custom expression for this calculation in spotfire?

2018     OTC
2017     InProgress
2017     InProgress
2017     OTC
2016     Late
2016     OTC
2016     OTC
2016     OTC
2016     OTC
2016     Late
2016     Late
2016     OTC
2016     OTC
2016     InProgress
2016     InProgress
2016     OTC
2016     InProgress
2016     InProgress
2015     OTC
2015     OTC
2015     Late
2015     Late
2015     OTC
2015     OTC
Solution

  • If you want to insert a calculated column into your table, use this formula below. I made up the [Status] and [Year] columns since you didn't specify them in your data set.

    Count(If(Trim([Status])="Late",[Status])) over ([Year]) / Count() over ([Year]) as [Percent Late for the Year]

    If you are doing this in a cross table, then set your Horizontal Axis to [Status], set your Vertical Axis to [Year] and then set the Cell Values to:

    Count() THEN [Value] / Sum([Value]) OVER (All([Axis.Rows]))

    In both cases, I wouldn't multiple by 100 as you did in your question. Instead, change the formatting of the column to PERCENTAGE. You can accomplish this for the first solution (inserting the column) by going to Edit > Column Properties > Formatting and selecting Percentage. In a cross table Right Click > Properties > Formatting and select Percentage for the Axis Values.