Charting table results

Anonymous — Sat, 01 Sep 2012 09:03:18 GMT

I can get exactly what I want in a pivot table. First dimension ReturnDate. Second dimension PortfolioName.

Unfortunately, when I convert to a chart (line), it breaks down and gives the incorrect results. I assume it is because my expression includes Above().

if(Above(Column(ColumnNo()),1)<1,if(Above(Column(ColumnNo()),1)*(1+Return)>1,1,Above(Column(ColumnNo()),1)*(1+Return)),if(Return>0,1,1+Return))

Any help would be greatly appreciated.

Thanks.

Joao.

Re: Charting table results

whiteline — Sat, 01 Sep 2012 09:14:43 GMT

It seems, when your convert your pivot to chart, the table that stands invisible behind the chart has vertical oriented structure.

Just transform your pivot (to the defalut view), then correct your expressions to get the right resuls.

I think you should change ColumnNo() to RowNo() and Above() to Before() or somthing like that.

Re: Charting table results

Anonymous — Sat, 01 Sep 2012 09:35:09 GMT

You are correct about the vertical oriented structure (I think). See table results below.

I'm not sure what you mean by transform the pivot to the default view? Do I make it horizontal? If so, doesn't PortfolioName become the x-axis in my graph and the graph become meaningless.

Also, I can change ColumnNo to RowNo, and Above to Before. What does Column become? Why is there no row equivalent??

Thanks again for all your help.

ReturnDate	Portfolio Name	ABC	DEF	GHI	KLM
2006-06-30		100.00%	100.00%	100.00%	100.00%
2006-07-31		100.00%	100.00%	100.00%	99.12%
2006-08-31		100.00%	100.00%	100.00%	100.00%
2006-09-30		100.00%	100.00%	100.00%	100.00%
2006-10-31		100.00%	100.00%	100.00%	100.00%
2006-11-30		100.00%	100.00%	100.00%	100.00%
2006-12-31		100.00%	100.00%	100.00%	100.00%
2007-01-31		100.00%	100.00%	100.00%	100.00%
2007-02-28		100.00%	100.00%	100.00%	100.00%
2007-03-31		100.00%	100.00%	100.00%	100.00%
2007-04-30		100.00%	100.00%	100.00%	100.00%
2007-05-31		100.00%	98.54%	99.85%	100.00%
2007-06-30		98.32%	97.18%	97.81%	98.75%
2007-07-31		97.15%	97.11%	97.77%	98.76%
2007-08-31		98.16%	96.91%	99.28%	99.58%
2007-09-30		100.00%	97.66%	100.00%	100.00%
2007-10-31		100.00%	100.00%	100.00%	100.00%
2007-11-30		96.48%	100.00%	94.56%	94.96%
2007-12-31		94.01%	97.14%	91.79%	91.76%
2008-01-31		88.83%	87.71%	84.38%	83.55%
2008-02-29		97.38%	96.65%	92.58%	93.48%
2008-03-31		95.35%	95.46%	89.25%	89.61%
2008-04-30		96.84%	98.50%	93.06%	92.15%
2008-05-31		99.22%	100.00%	95.54%	93.55%
2008-06-30		97.13%	92.24%	88.99%	88.53%
2008-07-31		94.13%	90.83%	87.67%	88.47%
2008-08-31		96.67%	91.90%	90.80%	92.33%
2008-09-30		91.35%	87.26%	83.17%	83.27%
2008-10-31		85.92%	80.44%	75.58%	75.28%
2008-11-30		87.69%	81.14%	74.67%	75.13%
2008-12-31		90.61%	84.58%	76.65%	77.11%
2009-01-31		88.16%	84.47%	73.71%	74.07%
2009-02-28		81.59%	77.57%	66.83%	67.75%
2009-03-31		89.44%	81.80%	73.14%	72.71%
2009-04-30		92.18%	80.92%	75.93%	75.54%
2009-05-31		98.39%	89.39%	82.00%	79.52%
2009-06-30		99.40%	86.43%	82.38%	80.37%
2009-07-31		100.00%	93.58%	90.50%	87.19%
2009-08-31		100.00%	97.62%	94.62%	91.29%
2009-09-30		100.00%	97.84%	94.57%	92.93%
2009-10-31		100.00%	100.00%	100.00%	96.98%
2009-11-30		98.93%	100.00%	99.16%	95.80%
2009-12-31		100.00%	100.00%	100.00%	100.00%
2010-01-31		98.80%	99.23%	98.36%	99.14%
2010-02-28		100.00%	99.31%	99.65%	100.00%
2010-03-31		100.00%	100.00%	100.00%	100.00%
2010-04-30		100.00%	100.00%	100.00%	100.00%
2010-05-31		96.22%	97.06%	95.82%	96.01%
2010-06-30		93.86%	96.78%	93.08%	93.76%
2010-07-31		100.00%	100.00%	100.00%	100.00%
2010-08-31		97.18%	98.69%	97.68%	97.26%
2010-09-30		100.00%	100.00%	100.00%	100.00%
2010-10-31		100.00%	100.00%	100.00%	100.00%
2010-11-30		99.99%	99.77%	99.06%	100.00%
2010-12-31		100.00%	100.00%	100.00%	100.00%
2011-01-31		97.23%	98.09%	96.52%	97.45%
2011-02-28		99.35%	100.00%	99.07%	99.27%
2011-03-31		99.54%	100.00%	100.00%	100.00%
2011-04-30		100.00%	100.00%	100.00%	100.00%
2011-05-31		99.15%	99.65%	99.81%	99.99%
2011-06-30		98.37%	97.64%	98.20%	98.52%
2011-07-31		97.65%	97.03%	96.54%	98.46%
2011-08-31		97.16%	97.54%	96.15%	98.45%
2011-09-30		95.33%	96.99%	93.38%	96.16%
2011-10-31		100.00%	100.00%	100.00%	100.00%
2011-11-30		100.00%	100.00%	100.00%	100.00%
2011-12-31		99.23%	98.90%	99.27%	98.80%
2012-01-31		100.00%	100.00%	100.00%	100.00%
2012-02-29		100.00%	100.00%	100.00%	100.00%
2012-03-31		99.50%	99.03%	99.95%	100.00%
2012-04-30		100.00%	100.00%	100.00%	100.00%
2012-05-31		95.98%	98.17%	96.46%	95.34%
2012-06-30		97.83%	100.00%	98.06%	97.90%
2012-07-31		100.00%	100.00%	100.00%	98.37%

Re: Charting table results

whiteline — Sat, 01 Sep 2012 10:42:56 GMT

By deafault I meant vertical-oriented. Replace your pivot with a straight table and try to make expressions to work right.

Re: Charting table results

Anonymous — Sun, 02 Sep 2012 09:01:56 GMT

Thanks. Got it to work by using TOTAL in the Above statement, and counting up the number selected in my second dimension.This allowed me to refer back to the previous value of my second dimension (PortfolioName).

topic Re: Charting table results in QlikView

Charting table results

Re: Charting table results

Re: Charting table results

Re: Charting table results

Re: Charting table results