606views

#Dr Spotfire : How to count ID #'s within a date range by date?

How to count ID #'s within a date range by date?

Below is the data table. I need date range between Start Date and End Dates to repeat the uniquecount of request id and build a graph where request id appears in every month between date ranges.

I am not sure if this is possible. My idea is to generate another table from source table - one row each for date (Request id, title and date) using TERR script.

And then build a bar chart using uniquecount(ID) and date. Is there any other way of doing it? I really do not want to pull this 2nd table creation off the spotfire.

Thank you for your support!

Data Table

ID	Title	Start date	End Date
1205	Title 1	06/04/2018	06/04/2018
1204	Ticket 2	05/04/2018	06/04/2018
1179	Change 1	12/03/2018	20/03/2018
1178	Change 2	12/03/2018	28/03/2018
1167	Change 3	20/02/2018	20/02/2018
1165	Change 5	19/02/2018	05/03/2018
1148	Ticket 8	22/01/2018	28/02/2018
1164	Change 6	13/02/2018	14/02/2018
1163	ticket 3	13/02/2018	14/03/2018
1162	Ticket 4	13/02/2018	28/02/2018
1161	Ticket 5	13/02/2018	23/02/2018
1160	Ticket 6	12/02/2018	14/03/2018
1159	Request 1	11/02/2018	23/02/2018
1158	Request 2	08/02/2018	14/03/2018
1157	Request 3	08/02/2018	14/03/2018
1156	Request 4	07/02/2018	14/03/2018
1155	Request 5	06/02/2018	14/03/2018
1154	Request 6	02/02/2018	14/03/2018
1153	Request 7	02/02/2018	05/03/2018
1152	Request 8	02/02/2018	05/03/2018
1151	Request 9	02/02/2018	28/03/2018
1149	Ticket 7	26/01/2018	28/03/2018
1148	Ticket 8	22/01/2018	28/02/2018

May 10, 2018 - 6:16am

snaik

Flag for Review

+ Add a Comment

(1) Answer

This can be achieved using data function like below where Savings, StartDate, EndDate are your inputs and out is output table parameter. You can modify data function to include more columns in your final table

library(lubridate)
stock=data.frame(Savings=Savings, Startdate=Startdate,Enddate=Enddate)
df = data.frame(Price=numeric(),Date = as.POSIXlt(character())) 
row=1
while (row <= nrow(stock)) {
 price=stock[row,1]
  st = stock[row,2]
  end=stock[row,3]
  values = seq(as.Date(paste(year(st),"/",month(st),"/01",sep="")), as.Date(paste(year(end),"/",month(end),"/01",sep="")), by = "months")
  df2=merge(price, as.POSIXlt(values))
  df <- rbind(df, df2)
  row=row+1
}
colnames(df) <- c("Savings","Date")
out2=df
df <- df[0,]