Difference in cumulative sums over different time columns

[Joana Guedes]

Dear community,

My data is structured as follows:

- rows represent items

- I have date columns with item creation date and item delivery date

I want to knowfor every month how many items created so far have not been delivered. Basically, for each month I need to compute the difference between the bar charts in the attachment.

I've been playing around with hierarchies and OVER functions but have not found a way to do this, Is it at all possible

The only way I've managed to achieve these calculations is by doing some data wrangling outside of Spotfire. But I come across this type of request so often that it has to be a common calculation to carry out. I'm hoping it can be achieved in Spotfire in a more flexible way.

 

Thank you for your help! :)

[Fabian Duerr]

Simply create a new calculated that returns 1 when the delivery date is empty or when the month of the delivery date is not equal the month of creation date. Depending on the question you try to answer... Else return 0.

Then use this column in a bar chart. That's it!

If([delivery date] is null, 1, 0)

 

If(month([delivery date]) = month([creation date]), 0, 1)