Count the number of occurrences of a specific string within a specific date range

[Pamir Rahimzadeh]

Hi all,

 

I am trying to add KPIs to my dashboard using a string column; the column can take on various values, so I want to count the number of instances of a specific string value, quarter by quarter. For example, if a column can take on the string values "A", "B", and "C", I want to count all instances of "A" in a quarter (date), and use this as a KPI, where more than a specific number of instances for "A" will cause a flag (for Spotfire, this would be encoded by a color change for the KPI tile). It would be a bonus if I could also count the instances of "A" as a proportion of all values (A, B, and C, for this example).

I -think- this is doable via "OVER" and "THEN" statements, but I am having a hard time understanding custom expressions, and I have not been able to find an answer to this question in other community posts.

[Fabian Duerr]

Actually you don't have to add any calculated column. All these calculations can be done with the KPI chart.

Go to the properties of you chart -> KPIs -> Settings

For the upper chart the expression for x-axis should beBinByDateTime([DateCol],"Year.Quarter",1) and for y-axis count()

For the lower chart the expression for x-axis should beBinByDateTime([DateCol],"Year.Quarter",1) and for y-axis Count() THEN [Value] / Sum([Value]) OVER (All([Axis.Tile]))

I know that these expression might look scary to someone. But they are auto-generated with just a few clicks.