Charting table results

[Anonymous]

Hi

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.

[whiteline]

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.

[Anonymous]

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%

[whiteline · Accepted solution]

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

[Anonymous]

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