splitting up time periods in quarters

[Sundaram Sridharan]

I have a rather challenging (to me) data analysis need: I have the following table

 

 

 

Name

#Subjects

Time Begin

Time End

 

 

Name1

80

10-Oct-17

4-Feb-20

 

 

Name2

60

3-Mar-19

5-Jul-21

 

 

 

 

I need to divide the #Subjects column evenly amongs all quarters between the start and end dates, like below

 

 

 

Name

#Subjects

Time Begin

Time End

quarter

 

 

Name1

10

10-Oct-17

5-Aug-19

4Q17

 

 

 

10

10-Oct-17

5-Aug-19

1Q18

 

 

 

10

10-Oct-17

5-Aug-19

2Q18

 

 

 

10

10-Oct-17

5-Aug-19

3Q18

 

 

 

10

10-Oct-17

5-Aug-19

4Q18

 

 

 

10

10-Oct-17

5-Aug-19

1Q19

 

 

 

10

10-Oct-17

5-Aug-19

2Q19

 

 

 

10

10-Oct-17

5-Aug-19

3Q19

 

 

Name2

5.45

3-Mar-19

5-Jul-21

1Q19

 

 

 

 

The quarter column has to be a date column - I eventually want to create bar charts that plot sum(#subjects) for each quarter across all names. Is there a way to do this inside spotfire

[Khushboo Rabadia]

You can use TERR/R script to wrangle the data using data functions.

1) Create starting and ending quarter dates calculated columns

3. Insert > Calculated Column...

Column name: Startdate

Expression: case when Quarter([TimeBegin])=1 then Date(Year([TimeBegin]),3,1) when Quarter([TimeBegin])=2 then Date(Year([TimeBegin]),6,1) when Quarter([TimeBegin])=3 then Date(Year([TimeBegin]),9,1) else Date(Year([TimeBegin]),12,1) end

 

4. Insert > Calculated Column...

Column name: Enddate

Expression: case when Quarter([TimeEnd])=1 then Date(Year([TimeEnd]),3,1) when Quarter([TimeEnd])=2 then Date(Year([TimeEnd]),6,1) when Quarter([TimeEnd])=3 then Date(Year([TimeEnd]),9,1) else Date(Year([TimeEnd]),12,1) end2) Create another calculated column for calculating new subject dividing it by each quarter

Qtrdiff = DateDiff("quarter",[startdate],[Enddate]) + 1

 

Newsubject = [subject]/[Qtrdiff]3) Then Create data function which will transform one row into multiple rows with date for each quarter between start and end dates.

# Load data.table package

library(data.table)

df

[Sundaram Sridharan]

I am new to TERR but have exposure to other structured languages like perl. Thanks for opening the door on this. Will learn and implement this.