What is wrong with this syntax within a custom expression

[Rachel Nygard]

Hello,

I am trying to use this in a custom expression within a cross table visualization:

If([CSN ID] is null,Sum([ROZU Cnt]),UniqueCount([CSN ID]))

CSN ID is a long integer and a unique identifer for a visit--I'm using it as a way to get the count of visits that may span 2 appointment slots, and thus be duplicated in the data. ROZU Cnt is a number that counts the number of slots. My goal was to create a metric that shows the number of appointments on the schedule if it is a filled slot, or the number of slots available if it is an unfilled slot.

I also tried a case when statement without success. They don't give me an error message in the custom expression box, but says 'please specify an aggregation method' in the visulaization and turns the statement red...I have used the if statement before with the sum(if(...but the aggregation method is dependent on which part of the if argument is true...

I'm by no means an expert Spotfire user, so hoping it is a simple answer.

Thanks in advance, and 10 points of good samaratin work to support COVID, as, if you haven't guessed, this is for healthcare related scheudling data to help match supply and demand.

[Shashank Gurnalkar]

Cross table expects aggregated data in all means which is evaluated based on what columns you select in the Horizontal axis and Vertical Axis. I see that the Condition is not aggregated, so Cross table does not know what to do with [CSN ID] (whether to take its Sum, or Average, or Count, etc.). Try the following expression to make it work.

If(First([A]) is NULL,Sum(),UniqueCount([C]))

[Rachel Nygard]

Thank you--that seemed to have done the trick!