topic Re: Changing a pivot table with two dimension and one expression into a line graph having one line for each dimension value in QlikView

Changing a pivot table with two dimension and one expression into a line graph having one line for each dimension value

phoenix — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have below pivot table

I need to convert this to line graph like below, each line representing a value of dimension2

Currently I am exporting the pivot to excel and creating the graph, how can I do it in qlikview? I cannot have separate expression for each year in the pivot table, it has to be only one expression.

Final result I would need from this is to identify the point in the graph where the percent is 50%, in the above example it is around 17.

How can I extract this information from the pivot or graph? so when the user makes his selection, not only he is seeing the graph but also the age at which it becomes 50%.

Thanks

Re: Changing a pivot table with two dimension and one expression into a line graph having one line for each dimension value

settu_periasamy — Thu, 24 Aug 2017 15:10:39 GMT

Hi Siva,

Can you provide the sample excel file?

Re: Changing a pivot table with two dimension and one expression into a line graph having one line for each dimension value

phoenix — Thu, 24 Aug 2017 15:18:23 GMT

TOB	2011	2012	2013	2014	2015	2016	2017
0	92.94%	93.12%	94.18%	94.61%	94.49%	93.49%	93.64%
1	89.94%	90.33%	91.57%	92.60%	92.63%	91.72%	91.52%
2	86.84%	87.47%	88.53%	89.46%	89.55%	88.85%	88.53%
3	83.60%	84.63%	85.47%	86.33%	86.50%	85.94%	85.62%
4	80.36%	81.80%	82.51%	83.25%	83.48%	83.03%	82.63%
5	76.98%	78.73%	79.47%	80.15%	80.40%	80.11%	79.42%
6	73.58%	75.51%	76.51%	77.12%	77.39%	77.21%	76.32%
7	70.74%	72.79%	73.84%	74.36%	74.69%	74.57%	-
8	68.05%	70.15%	71.21%	71.66%	72.00%	72.00%	-
9	65.44%	67.52%	68.57%	68.96%	69.34%	69.44%	-
10	62.79%	64.88%	65.95%	66.27%	66.72%	66.90%	-
11	60.17%	62.24%	63.29%	63.55%	64.07%	64.31%	-
12	57.76%	59.72%	60.65%	60.89%	61.43%	61.75%	-
13	55.47%	57.32%	58.12%	58.36%	58.89%	59.26%	-
14	53.13%	55.01%	55.71%	55.87%	56.45%	56.81%	-
15	50.81%	52.74%	53.33%	53.44%	54.03%	54.52%	-
16	48.55%	50.50%	51.02%	51.09%	51.68%	52.26%	-
17	46.32%	48.35%	48.76%	48.77%	49.38%	50.16%	-
18	44.16%	46.27%	46.52%	46.51%	47.14%	48.09%	-
19	42.02%	44.18%	44.37%	44.29%	44.93%	-	-
20	39.94%	42.16%	42.21%	42.10%	42.76%	-	-
21	37.90%	40.15%	40.07%	39.96%	40.61%	-	-
22	35.89%	38.18%	37.94%	37.82%	38.48%	-	-
23	33.90%	36.19%	35.81%	35.69%	36.36%	-	-
24	31.95%	34.23%	33.75%	33.65%	34.28%	-	-
25	30.16%	32.39%	31.80%	31.66%	32.30%	-	-
26	28.47%	30.64%	29.91%	29.81%	30.37%	-	-
27	26.82%	28.97%	28.10%	28.02%	28.55%	-	-
28	25.22%	27.30%	26.35%	26.30%	26.77%	-	-
29	23.72%	25.71%	24.64%	24.59%	25.03%	-	-
30	22.27%	24.18%	23.00%	22.96%	23.53%	-	-
31	20.85%	22.70%	21.41%	21.39%	-	-	-
32	19.51%	21.24%	19.86%	19.85%	-	-	-
33	18.18%	19.81%	18.36%	18.37%	-	-	-
34	16.90%	18.44%	16.90%	16.89%	-	-	-
35	15.70%	17.09%	15.51%	15.48%	-	-	-
36	14.62%	15.84%	14.23%	14.18%	-	-	-
37	13.71%	14.75%	13.12%	13.01%	-	-	-
38	12.88%	13.72%	12.09%	11.93%	-	-	-
39	12.07%	12.73%	11.10%	10.93%	-	-	-
40	11.30%	11.80%	10.16%	10.04%	-	-	-
41	10.56%	10.90%	9.25%	9.26%	-	-	-
42	9.83%	10.03%	8.39%	8.41%	-	-	-
43	9.14%	9.20%	7.54%	-	-	-	-
44	8.48%	8.42%	6.73%	-	-	-	-
45	7.81%	7.65%	5.97%	-	-	-	-
46	7.18%	6.90%	5.22%	-	-	-	-
47	6.55%	6.23%	4.56%	-	-	-	-
48	5.97%	5.65%	4.07%	-	-	-	-
49	5.44%	5.12%	3.63%	-	-	-	-
50	4.93%	4.64%	3.21%	-	-	-	-
51	4.45%	4.18%	2.83%	-	-	-	-
52	4.01%	3.75%	2.45%	-	-	-	-
53	3.58%	3.36%	2.07%	-	-	-	-
54	3.18%	3.01%	1.78%	-	-	-	-
55	2.81%	2.68%	-	-	-	-	-
56	2.45%	2.36%	-	-	-	-	-
57	2.13%	2.06%	-	-	-	-	-
58	1.84%	1.78%	-	-	-	-	-
59	1.57%	1.53%	-	-	-	-	-
60	1.37%	1.36%	-	-	-	-	-
61	1.23%	1.22%	-	-	-	-	-
62	1.12%	1.12%	-	-	-	-	-
63	1.01%	1.04%	-	-	-	-	-
64	0.93%	0.97%	-	-	-	-	-
65	0.85%	0.89%	-	-	-	-	-
66	0.78%	0.85%	-	-	-	-	-
67	0.71%	-	-	-	-	-	-
68	0.65%	-	-	-	-	-	-
69	0.60%	-	-	-	-	-	-
70	0.55%	-	-	-	-	-	-
71	0.50%	-	-	-	-	-	-
72	0.45%	-	-	-	-	-	-
73	0.41%	-	-	-	-	-	-
74	0.39%	-	-	-	-	-	-
75	0.37%	-	-	-	-	-	-
76	0.34%	-	-	-	-	-	-
77	0.33%	-	-	-	-	-	-
78	0.29%	-	-	-	-	-	-
79	-	-	-	-	-	-	-
80	-	-	-	-	-	-	-
81	-	-	-	-	-	-	-
82	-	-	-	-	-	-	-
83	-	-	-	-	-	-	-
84	-	-	-	-	-	-	-
85	-	-	-	-	-	-	-
86	-	-	-	-	-	-	-
87	-	-	-	-	-	-	-
88	-	-	-	-	-	-	-
89	-	-	-	-	-	-	-
90	-	-	-	-	-	-	-

Re: Changing a pivot table with two dimension and one expression into a line graph having one line for each dimension value

phoenix — Thu, 24 Aug 2017 15:28:22 GMT

I have converted the percentages to zero decimal and highlighted using background color ->

=if([result3]>=0.49 and [result3]<=0.514,lightred())

Now I just need to take average of the TOB's where it went close to 50%. So here it would be (15*1)+(16*3)+(17*2)/6 = 16.66

Re: Changing a pivot table with two dimension and one expression into a line graph having one line for each dimension value

settu_periasamy — Fri, 25 Aug 2017 17:55:23 GMT

Hi,

Maybe like attached

Re: Changing a pivot table with two dimension and one expression into a line graph having one line for each dimension value

phoenix — Fri, 25 Aug 2017 18:38:24 GMT

Perfect! Thanks settu_periasamy‌ that works. that solves second issue. My first issue was to convert pivot table into a line graph. In my pivot I am using above() and rangesum() functions to do some calculations, so when I convert pivot to line graph its is not giving correct results because it is internally converting the table to straight able and above() is miscalculating. thought there would be a work around to directly convert a pivot to line graph without changing the table structure. Thanks!

Sorry I am not able to send the data I am working on, but below is the pivot structure.

pivot table structure:

dimensions:

TOB_calculated

Vintage_yr

expressions:

result3:

if(TOB_calculated <= MAX_TOB_VINTAGE_YR,

if(MAX_LOAN_VINTAGE_MTH>=12 or TOB_calculated = 0,
(sum(UNPD_PRIN_BAL_AMT)/SUM (ORG_PRIN_AMT)),
if(rank(-if(MAX_LOAN_VINTAGE_MTH<12,TOB_calculated))=1,
( above(sum(UNPD_PRIN_BAL_AMT)/SUM (ORG_PRIN_AMT))
+
(sum(UNPD_PRIN_BAL_AMT)-above(if(MAX_LOAN_VINTAGE_MTH>12, sum(UNPD_PRIN_BAL_AMT), sum(if(MAX_TOB_VINTAGE_YR_MTH > TOB_calculated, UNPD_PRIN_BAL_AMT)))))/SUM (ORG_PRIN_AMT)),
RangeSum(Above([result3]),(sum(UNPD_PRIN_BAL_AMT)-above(if(MAX_LOAN_VINTAGE_MTH>12, sum(UNPD_PRIN_BAL_AMT), sum(if(MAX_TOB_VINTAGE_YR_MTH > TOB_calculated, UNPD_PRIN_BAL_AMT)))))/SUM (ORG_PRIN_AMT)))
),
null())

Until certain TOB_calculated for a given Vintage_yr, calculation is straight, but after I hit the limiting point, I need to refer back to the same expression(result3) for previous Tob_calculated and add the delta. That's where it gets complicated. wish I can do this without using above() and rangesum() function.