Equivalent of Sumproduct with a fixed Range

[Anonymous]

I have a Pivot Table in which I am earning the revenue for this month, which is dependent on the earnings from all of previous months. 

 I need to earn out my earnings in such a way that I am earning 1/12th of all the previous months in the present month, 

i.e. for the month of say, 2018 Dec, I will earn 1/12th of my earnings in 2018 Jan, Feb, until Dec, and this has to be dynamic. And this needs to be calculated for each month, i.e. from 2016 Jan onwards until 2018 YTD. Therefore, 2016 Jan will have 1/12th of revenue form 2015 Feb till 2016 Jan and so forth.

This is fairly straightforward in Excel but I am clueless on how to execute the same in qlikview. Kindly help.

[Anil_Babu_Samineni]

Can you please share the formula how it compute in Excel, So then we can try in Qlik

[Anonymous]

=SUMPRODUCT(D6:D18,'Earning Patterns'!$C$3:$C$15)

The contents of 'Earning Patterns'!$C$3:$C$15 are as follows: It may as well be 8.3% for 12 months, but the range of D6:D18 keeps changing. Effectively, what this means is, as of 2016 Dec, it would take 4.2% of 2015 Dec earnings, 8.3% of 2016 Jan earnings etc. As of 2017 Jan, it would take 4.2% of 2016 Jan, 8.3% of 2016 Feb, etc. The D6:D18 is a relative reference which keeps moving for every 12 months to D7:D19 and so on, and the other an absolute reference. 

In qlikview, I have a pivot table, in which I have a calculated dimension X, the other being the table I have attached below, which would be a dynamic reference. I want to get the product of X with this dynamic reference. Hope I have given an idea of what I seek. If not, kindly do revert so I may provide a better idea. 

Thank you in advance. 

	12
1	4.2%
2	8.3%
3	8.3%
4	8.3%
5	8.3%
6	8.3%
7	8.3%
8	8.3%
9	8.3%
10	8.3%
11	8.3%
12	8.3%
13	4.2%
14