topic Expression to calculate YTD return from monthly data in QlikView

Expression to calculate YTD return from monthly data

Mon, 30 Apr 2012 17:50:34 GMT

I have monthly returns and I convert each month’s return to a decimal and add 1 to each one to get the factor. So, it looks like this:

Col-A	Col-B	Col-C	Col-D
Date	Return%	Factor	YTD Return
1/1/2012	1.51%	1.0151	=C3
2/1/2012	-1.96%	0.9804	=C3*C4
3/1/2012	1.12%	1.0112	=C3C4C5
4/1/2012	4.43%	1.0443	=C3C4C5*C6
5/1/2012	3.49%	1.0349	=C3C4C5C6C7
6/1/2012	-1.66%	0.9834	=C3C4C5C6C7*C8
7/1/2012	-3.10%	0.969	=C3C4C5C6C7C8C9
8/1/2012	1.50%	1.015	=C3C4C5C6C7C8C9*C10
9/1/2012	3.74%	1.0374	=C3C4C5C6C7C8C9C10C11

I am trying to get the year-to-date returns, by the product for the entire set of factors like this:

1.0151 × 0.9804 × 1.0112 × 1.0443 x 1.0349 x 0.9834 x 0.9690 x 1.0150 x 1.0374 = 1.0913, so YTD for September is 9.13%

You can't use sum or total because that adds the factors together, so what function should I use to get the product of the data set?

Re: Expression to calculate YTD return from monthly data

lironbaram — Tue, 01 May 2012 07:52:49 GMT

hi attach is an example hope it helps you

you should note that this example only works if the date is the only dimension in the table

so other all additional data should be expressions

Re: Expression to calculate YTD return from monthly data

Tue, 01 May 2012 16:09:40 GMT

Hi Iiron,

Thanks for your help. I only have the personal version of QV and can't view your attached file. Can you please write out your expression for YTD return?