Jump to content
We've recently updated our Privacy Statement, available here. ×

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

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...