topic Re: Average date differnce in Pivot table in QlikView

Average date differnce in Pivot table

Anonymous — Mon, 11 Sep 2017 21:04:08 GMT

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

Re: Average date differnce in Pivot table

sunny_talwar — Mon, 11 Sep 2017 21:14:28 GMT

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

Re: Average date differnce in Pivot table

Anonymous — Mon, 11 Sep 2017 21:26:26 GMT

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

Re: Average date differnce in Pivot table

Anil_Babu_Samineni — Tue, 12 Sep 2017 01:53:55 GMT

May be these?

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

Still not sure, your intention here

Re: Average date differnce in Pivot table

effinty2112 — Tue, 12 Sep 2017 10:39:51 GMT

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

Re: Average date differnce in Pivot table

Anonymous — Tue, 12 Sep 2017 14:42:12 GMT

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

Re: Average date differnce in Pivot table

Anonymous — Tue, 12 Sep 2017 14:56:10 GMT

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

Re: Average date differnce in Pivot table

effinty2112 — Tue, 12 Sep 2017 16:00:24 GMT

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

Re: Average date differnce in Pivot table

Anonymous — Tue, 12 Sep 2017 16:05:23 GMT

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