Jump to content

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

Recommended Posts

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

Link to comment
Share on other sites

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:





Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...