Set column name conditionally in cross table

[Michael Zaragoza]

I need to set the column name in a cross table conditionally. I've tried the following two ways but Spotfire objects to what follows after the "AS" keyword. My two attempts:

Count(DISTINCT [serialNum]) AS if([TYPE]~="WS", [initialProd],[FinalProd])

Also tried:

CASE

WHEN [TYPE]~="WS" THEN

Count(distinct [serialNum]) AS [initialProd]

ELSE

Count(distinct [serialNum]) AS [FinalProd]

END

Note: the horizontal axis is set to (Column Names).

Is there a way to do this

[James Watts]

I don't think you can do it the way you are trying - potentially, one column would be split in two.

 

Instead, I think you could do it with a series of calculated columns, with the first one a simple if statement to produce text:

 

if([TYPE]~="WS", "InitialProd","FinalProd")

 

Then do your calculation over this calculated column.