topic Re: Pivot table showing values incorrect? in QlikView

Pivot table showing values incorrect?

Mon, 25 Jan 2016 11:27:55 GMT

Hi,

I have below straight table in qvw.

CustomerID	Open to Close	Close to Reenter	Reenter To Warehouse	Open To Staff
12	52	18.2	102	116
13	63	17.0	99	98
14	107	48.0	102	95
15	54	30.5	200	67
16	79	20.9	106	48
17	80	29.3	91	109
18	108	42.4	82	32
19	80	30.7	84	123
20	78	13.5	75	55
21	106	17.2	93	119
22	45	34.9	98	92
23	65	42.2	97	46
24	42	22.8	99	55
20	104	20.8	96	116
3	39	20.3	92	137
4	104	24.1	108	70
65	59	28.7	102	131
89	73	14.1	64	93
90	62	34.9	84	138
121	80	38.8	77	40
100	26	12.3	90	98
1	91	78.6	97	49
2	25	17.5	93
3	63	12.1	85	40
4	51	18.9	75
5	28	19.7	70
78	41		91
98	30		95
22	94		94
11	22		78
55	27		82
778	33		85
66	66		85
44	36		82
3333
231
321			30
121			140
1000			145

I have to create pivot table as below in qvw

Data	Open to Close	Close to Reenter	Reenter To Warehouse	Open To Staff
Min	22	12	30	32
25%	40	18	82	52
Median	63	22	92	93
75%	80	34	99	116
Max	108	79	200	138
IQR	41	16	17	64

Min = minimum(Open to Close)

25% = PERCENTILE(Open to Close,0.25)

Median = =MEDIAN(Open to Close)

75% = PERCENTILE(Open to Close,0.75)

Max= maximum(Open to Close)

IQR = 75%-25%

similarly same formulas for other columns.

I created pivot table using ValueList('Open to Close','Close to Reenter','Reenter to Warehouse','Open to Staff') as dimension but values are showing wrong. is there any way that I can do this correctly.

please can anyone suggest me.

Thanks.

Re: Pivot table showing values incorrect?

Mon, 25 Jan 2016 12:45:27 GMT

Can anyone suggest me please how to sort out the pivot table with correct values.

Re: Pivot table showing values incorrect?

PradeepReddy — Mon, 25 Jan 2016 13:36:25 GMT

see the attachment.. it might be helpful for you..

Re: Pivot table showing values incorrect?

Mon, 25 Jan 2016 13:46:11 GMT

Thanks. how can I use cross table by using calculated values of straight table? I need out put shown like pivot table by considering values of straight table.

Re: Pivot table showing values incorrect?

Mon, 25 Jan 2016 16:08:01 GMT

I am really struggling to get this done. Any help please

Re: Pivot table showing values incorrect?

jduenyas — Mon, 25 Jan 2016 19:15:21 GMT

Is this acceptable?

Re: Pivot table showing values incorrect?

PradeepReddy — Mon, 25 Jan 2016 19:28:46 GMT

Without Calculated dimensions, we can showcase something like in the attachment...

Re: Pivot table showing values incorrect?

ecolomer — Mon, 25 Jan 2016 19:38:32 GMT

Is this you need?

Re: Pivot table showing values incorrect?

jduenyas — Mon, 25 Jan 2016 19:41:37 GMT

Actually, Pradeep's solutions are correct and by far better than mine.

You may want to close this one and mark his solution as correct.

Re: Pivot table showing values incorrect?

Wed, 03 Feb 2016 21:27:34 GMT

Thanks. what I mean is the values I have in straight table is calculated using expressions. then how can I use cross function for these calculated values. I understand your solution. the solution which you provided is taken from the values that I added in post. but this is sample data. the values which I have is calculated using expressions in straight take itself. I can not take the values every time to excel file or inline table to use cross function.

Please let me know how can I get this?

sorry if I am not clear in my explanation

Re: Pivot table showing values incorrect?

PradeepReddy — Thu, 04 Feb 2016 05:58:40 GMT

Can you please share sample QVW

Re: Pivot table showing values incorrect?

Thu, 04 Feb 2016 11:17:36 GMT

Sorry I do not have sample file. But I can provide script that I used. Below is the one I used

Num(max(if(STATUS='Close',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))-max(if(STATUS=Open',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))) as [Open to Close],

Num(max(if(STATUS='Reenter',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))-max(if(STATUS=Close',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))) as [Close to Reenter],

Num(max(if(STATUS=Warehouse',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))-max(if(STATUS='Reenter',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))) as [Reenter to Warehouse],

Num(max(if(STATUS='Staff',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))-max(if(STATUS='Open',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))) as [Open to Staff],

If(Num(max(if(STATUS='Close',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))-max(if(STATUS='Open',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))),'Open
to Close',

if(Num(max(if(STATUS='Reenter',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))-max(if(STATUS='Close',DATE(LEFT (DateUsed,10),'DD/MM/YYYY')))),'Close to Reenter',

If(Num(max(if(STATUS='Warehouse',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))-max(if(STATUS='Reenter',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))),'Reenter
to Warehouse',

If(Num(max(if(STATUS='Staff',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))-max(if(STATUS='Open',DATE(LEFT(DateUsed,10),'DD/MM/YYYY')))),'Open to Staff')))) as F

by using 'F' I am following your solution but data is showing correct

Please help me