Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Equivalent of Sumproduct with a fixed Range

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.

 

Labels (2)
2 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

=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
14.2%
28.3%
38.3%
48.3%
58.3%
68.3%
78.3%
88.3%
98.3%
108.3%
118.3%
128.3%
134.2%
14