Jump to content
We've recently updated our Privacy Statement, available here. ×

What is wrong with this syntax within a custom expression

Rachel Nygard

Recommended Posts


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.

Link to comment
Share on other sites

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]))

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...