CODEWITHSUNDEEP
spotfirespotfire-analystspotfire-webplayer
iain
iain

Reputation: 1

Spotfire Counter for values in visualisation >0

I am building a Spotfire page which displays averages over a large dataset, but when pulling in a large amount of data there are some values which are 0. this is not the issue as the dataset is based on hours and targeted hours. when I create a calculated value for an average over the dataset within the visualization I can only get the count of entries, which throws the average off if there are only 2 values > 0 but there are 10 entries. ideally I would like a counter to count for the entries within the visual that are >0 so I can divide it by the SUM of the 2 positive entries within the dataset.

tried to use a counter to count for values >0 but getting error as ">" is not supported within Aggregation within Calculated Value

Upvotes: 0

Views: 41

Answers (1)

Gaia Paolini
Gaia Paolini

Reputation: 1452

Try to surround your expression with a CASE WHEN END something like:

MAX( CASE WHEN [Hours]>0 THEN Sum([Hours] / UniqueCount([SerialNo]) END )

Without the MAX(..) it will define your average only when [Hours] is greater than zero. So the call to MAX defines the column for all rows.

Note: It would be easier to read if you had updated your question rather than entering the expression in a comment.

Upvotes: 0

Related Questions