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

[Khushboo Rabadia]

Can you provide that sample data in .xls file

[Muhammad Uzair Khalid]

Sample data attached as Excel sheet

[Khushboo Rabadia]

You can create a data function using TERR script such that it will create multiple rows for each month between start date and end date so KRI can be counted easily and added month column can be used in X-Axis of bar chart.

Attached is the dxp showing the approach.

Calculated column -Date(case when [sTATUS CATEGORY]="FINAL" then [sTATCHEDATE] else DateTimeNow() end)

Data function script:

library(lubridate)

stock=data.frame(maindt)

stock2=data.frame(Enddate=enddt)

df2