CODEWITHSUNDEEP
expressionspotfire
user3111598
user3111598

Reputation: 13

Spotfire Expression Value for Max(Row Count)

I'm trying to make a Calculated Value Control expression on the below columns:

Row Count | Date | Value

What I want to get is the Value for the 'newest' date, which will also be the highest row count. How can I write an expression to get this, it seems like it ought to be simple. I'm having trouble writing it in only expression language, without SQL.

Upvotes: 1

Views: 4907

Answers (1)

clesiemo3
clesiemo3

Reputation: 1129

Using the expression below you can limit the records in your table to those with the highest (aka most recent) date which should have your Value of interest.

[Date] = Max([Date])

You can do the same with row count since you mentioned the record of interest being the highest row count:

[Row Count] = Max([Row Count])

If you're looking to create a calculated column you can use a case statement to spit out the value:

case when [Date] = Max([Date]) then [Value] end

Lastly, should you want to display this value in a Text Area to show off your value you can utilize the calculated column above:

1) Create a new Text Area

2) Type some text about what it is: "Value for newest Date: " (optional)

3) Click "Insert Dynamic Item" -> "Calculated Value"

4) Under "Data" ensure the appropriate data table is selected. Note: You can uncheck the "Use Current Filtering..." box here if you do not want your value to update as you filter.

5) Under Values, utilize our calculated column with "Max" wrapped around it to avoid Summing duplicate values:

Max(CALCULATED_COLUMN)

Here is a screenshot of my work with random filler dates and values: https://i.sstatic.net/Kw51R.png

The larger text is to show the calculated value dynamic items. I used Max([Date]) for the date value.

Upvotes: 2

Related Questions