Pivoting to align dates

[Corporate Analytics]

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

[Khushboo Rabadia]

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: