Need help in writing expression

[Balakumar Krishnasamy]

Hi All,

I am pretty new to spotfire.

I want to write an expression like this in spotfire.

Sum([score]) When [GameType]="Cricket"

what are the different menthods to write it

Can anyone suggest me

Thanks in advance!

Regards,

Bala

[Colin Gray 3]

Hi there,

 

Spotfire uses an expression language which is quite similar to other tools. So there are two options for this type of test which are IFs and CASE statements. You can use the IF expression which would be:

 

Sum(if([GameType]="Cricket",[score]))

 

In the statement above I am leaving out the option of what to do if the game is not Cricket. If you want to sum a different column when the game isn't cricket you can do:

 

Sum(if([GameType]="Cricket",[score], [some Other Column]))

 

To summarise, you are using an IF statement ofif([GameType]="Cricket",[score])and then putting this inside the SUM statement, so Spotfire will filter out any row that is not Cricket and then sum the Score column.

 

The alternative method is the CASE statement in Spotfire. This allows for a series of options to test for. Your case statement would look like:

 

CASE

When [GameType]="Cricket"then SUM([score])

ELSE

NULL

END

With a CASE statement you can add as many 'When's as you like so you can easily have many conditions, which are evaluated in sequence.

 

For your example, using IF is simpler as it only has one condition. You can put as many conditions as you like into an IF statement but I find when you have lots of conditions, a CASE statement can be easier to read.

 

Hope this helps

Colin

[Balakumar Krishnasamy]

Hi Colin,

 

Thanks for the answer. 

 

I have one more question. Is DataTimeNow() not working with MS SQL server data source

 

 

 

 

[Colin Gray 3]

No problem :)

 

Not sure what you mean by this You can use the DateTimeNow() in an expression for a calculated column on your data, or in a visual regardless of where the data came from. The exception I guess is if you are using in-database analytics with writing an expression on a chart.