Rachel Nygard Posted March 29, 2020 Share Posted March 29, 2020 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. Link to comment Share on other sites More sharing options...
Shashank Gurnalkar Posted March 30, 2020 Share Posted March 30, 2020 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 More sharing options...
Rachel Nygard Posted March 30, 2020 Author Share Posted March 30, 2020 Thank you--that seemed to have done the trick! Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now