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

Pivoting to align dates


Corporate Analytics

Recommended Posts

I have data that is organized by various status dates. I can easily pivot this in excel, because It is not neccessary to pick a column to sort the date, rather it aggregates by a generic date. In Spotfire, I can only chose one date for the axis. See below:

 

Current

 

 

 

Recruiter

Client

Application Date

Interview Date

Offer Date

Hire Date

 

 

A

1

1/1/2019

1/2/2019

1/3/2019

1/4/2019

 

 

B

1

2/1/2019

2/2/2019

2/3/2019

2/4/2019

 

 

A

2

NULL

1/2/2019

1/3/2019

1/4/2019

 

 

B

2

1/1/2019

1/2/2019

1/3/2019

NULL

 

 

 

 

Needed Transformation:

 

 

 

Recruiter

Client

Jan

Jan

Jan

Jan

Feb

Feb

Feb

Feb

 

 

Recruiter

Client

Applications

Interviews

Offers

Hires

Applications

Interviews

Offers

Hires

 

 

A

1

1

1

1

1

0

0

0

0

 

 

A

2

0

1

1

1

0

0

0

0

 

 

A

Subtotal

1

2

2

2

0

0

0

0

 

 

B

1

0

0

0

0

1

1

1

1

 

 

B

2

1

1

1

0

0

0

0

0

 

 

B

Subtotal

1

1

1

0

1

1

1

1

Link to comment
Share on other sites

  • 4 weeks later...

1) You can unpivot your dataset :

1. Select File > Add Data Tables...

Source: Data table from current analysis

Data table: Data Table

Update behavior: Automatic

Added transformations

Transformation name: Unpivot

Columns to pass through:

Recruiter

Client

Columns to transform:

Application Date

Interview Date

Offer Date

Hire Date

Category column name: Category

Category column data type: String

Value column name: Value

Value column data type: Date

Empty values included: Yes

2) Then in cross table, you can create 12 custom expressions in cell values like below for each month:

first(case when Month([Value])=1 then 1 else 0 end) as [Jan],

first(case when Month([Value])=2 then 1 else 0 end) as [Feb]

.

.

.

.

.

.

.That way resulting cross table will be as shown below:

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