Average date differnce in Pivot table

[Anonymous]

Hi friends ,

I have data in below format .I want to display average date difference in pivot table

Previous Stage	Current Stage	Previous Date	Current Date
S1	S2	Null	9/11/2017
S2	S3	9/11/2017	9/12/2017
S3	S4	9/12/2017	9/13/2017
S4	S5	9/13/2017	9/14/2017
S4	S5	9/6/2017	9/7/2017
S5	S6	9/7/2017	9/7/2017
S6	S7	9/7/2017	9/7/2017
S7	S8	9/7/2017	9/7/2017
S5	S4	9/5/2017	9/5/2017

Expected output format

stages	s1	s2	S3	S4	S5	S6	S7	S8
s1	x	x	x	x	x	x	x	x
S2	x	x	x	x	x	x	x	x
S3	x	x	x	x	x	x	x	x
S4	x	x	x	x	x	x	x	x
S5	x	x	x	x	x	x	x	x
S6	x	x	x	x	x	x	x	x
S7	x	x	x	x	x	x	x	x

[sunny_talwar]

What are the x's for? what do they mean?

[Anonymous]

HI Sunny,Sorry for in complete description .'X' represents Average date difference from stage to stage

changes in the stages are  an S1 should only go to an S2, and so on.

• S1 -> S2
• S2 -> S3
• ...
• S8 ->

but we still need to determine the average date diff for a movement from S1->S3 S1_->S4 ........

[Anil_Babu_Samineni]

May be these?

Avg(Stage)-Below(Avg(Stage))

Still not sure, your intention here

[effinty2112]

Hi Krishna,

Maybe this pivot table?

Previous Stage	Current Stage	S2	S3	S4	S5	S6	S7	S8
S1
S2			1
S3				1
S4					1
S5				0		0
S6							0
S7								0

Expression: Avg([Current Date]-[Previous Date])

Regards

Andrew

[Anonymous]

Hi Andrew,

thanks for your reply but this didn't solve my problem.I want to display the date diff averages for each and every stage .

From

s1 -> s2

s1 -> s3

s1 -> s4

s1 -> s5

....->  s8

but we get the data in linear fashion

Previous Stage	Current Stage	Previous Date	Current Date
S1	S2	Null	9/11/2017
S2	S3	9/11/2017	9/12/2017
S3	S4	9/12/2017	9/13/2017
S4	S5	9/13/2017	9/14/2017
S2	S3	9/08/2017	9/10/2017

[Anonymous]

Hi Anil,

I am trying to display average date difference from stage to stage

Previous Stage	Current Stage	Previous Date	Current Date
S1	S2	Null	9/11/2017
S2	S3	9/11/2017	9/12/2017
S3	S4	9/12/2017	9/13/2017
S4	S5	9/13/2017	9/14/2017

in the above table we don't have data for S2-S4 but we should be able to calculate the date diff (9/11/2017 - 9/13/2017) =2

[effinty2112]

Hi Krishna,

The pivot table gives the average date difference.

S2=>S3 is 1

S3=>S4 is 1

S4=>S5 is 1

S5=>S4 is 0

S5=>S6 is 0

S6=>S7 is 0

S7=>S8 is 0

[Anonymous]

But I want to determine the date diff from

s2-s4  s2-s5... s2-s8

s3-s5  s3-6...

s4-s6 ...s4-s8

...

as well