Muhammad Uzair Khalid Posted January 22, 2019 Share Posted January 22, 2019 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 More sharing options...
Khushboo Rabadia Posted January 28, 2019 Share Posted January 28, 2019 Can you provide that sample data in .xls file Link to comment Share on other sites More sharing options...
Muhammad Uzair Khalid Posted January 29, 2019 Author Share Posted January 29, 2019 Sample data attached as Excel sheet Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted January 29, 2019 Share Posted January 29, 2019 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now