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

[phoenix]

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

[settu_periasamy]

Hi Siva,

Can you provide the sample excel file?

[phoenix]

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

[phoenix]

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

[settu_periasamy]

Hi,

Maybe like attached

[phoenix]

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.