0

I'm struggling with calculating new columns as I'm still new to Spotfire.

I am given two Date columns (format: mm/dd/yyyy), one is "Created" and the other is "Closed" as shown below in the left two columns.

enter image description here

The end goal is to have two new columns (shown above in right two columns): 1. Date column with daily interval (format: mm/dd/yyyy) 2. Number of occurrences that where open on that day based on all rows in the "Created" and "Closed" columns

Hopefully this makes sense, please let me know if it does not. I really appreciate the help and look forward to learning - thanks!

jmeddy
  • 109
  • 1
  • 5
  • 16

1 Answers1

0

It seems like you are trying to add records with a calculation, which is not possible. Spotfire will only work with the data that is there.

You'll need to start with a table that has one record per day.

BMAnalytics
  • 114
  • 7
  • I was afraid of that. I was hoping to somehow create a column with dates (per day), then use that column and two provided columns to calculate how many items were open for that day.... any other ideas? – jmeddy May 12 '20 at 22:59
  • Can you just create those data values in excel (really far out or for whatever range you need), and bring that in as a table and join to it? – Mark P. May 13 '20 at 12:43
  • I think I can do that and I was thinking about that. Let's assume I can, I mainly need help in formulated a calculated column to compare to all historical values and count per day.. – jmeddy May 13 '20 at 21:03
  • I don't know if the assumption and the ask for calculation help make sense. So....I'm going to assume that you can get a table that has one record for every day. But after you get that, you would still need to join it to your other data set. In trying to think thru how to help you, I realized that given the information shown, there's not a logic column to join on without making it real weird. – BMAnalytics May 28 '20 at 22:32