How to count entries on monthly basis Such that same row/entry can be counted in multiple months if conditions are met...

[Muhammad Uzair Khalid]

I am trying to make a bar chart with X- axis showing timeline with respect to months (Jan, Feb, March etc). On Y-axis, I want to count how many [KRI] are there in each month based on their [status Category]

I have two conditions though:

1) If [status Category] is 'Final', count that KRI in all months between [TRIGGERDATE] month and [statchedate]. for example, same KRI should be counted in Jan, and Feb and March etc (from time it was open till the time/month it was closed).

2) If [status Category] is NOT Final, count that KRI in all months between [TRIGGERDATE] month and today's month.

In the picture below with the data sample, the pink colored blocks shows when the data should NOT be counted in that particular month.

I am trying to write a custom expression using if statement, but I am not able to figure out how would spotfire know that a KRI should be counted between [Triggerdate] and [statchedate] Or for instance, between [Triggerdate] and [today]

I am also thinking about adding a calculated column that will determine if the KRI should be counted in how many months (but this will be ongoing data, so I am afraid that my column would keep on expanding..)

Any suggestions would be very much appreciated :)