Muliply Previous Rows

[vishalj88]

Hi experts, 

Is there a way to multiply previous rows in QlikView? Let me explain. We have some sales data and we're looking to calculate a custom return metric for the day. This metric is calculated by multiplying the previous 3 days. I want to able to programitically achieve this result.

Product	Date	Sales	Return (Expected Result)	Comment
A	1-Jan	3	-	Blank because did not accumulate 3 days of historical data
A	2-Jan	4	-	Blank because did not accumulate 3 days of historical data
A	3-Jan	5	60	5*4*3
A	4-Jan	6	120	6*5*4
B	1-Jan	2	-	Blank because did not accumulate 3 days of historical data
B	2-Jan	3	-	Blank because did not accumulate 3 days of historical data
B	3-Jan	6	36	6*3*2
B	4-Jan	10	180	10*6*3

As you can see I've simplified the problem by looking at just 3 days. However, I have to calculate the metric to look at the previous 240 days. I was hoping there was some form of RangeSum but that doesn't seem to exist. Any one have any experience working with such a request?

Thanks

Vishal

[m_woolf]

Take a look at peek

[vishalj88]

Yes, I'm aware of Peek. How would i write such a function so i'm getting answer i'm looking?

[m_woolf · Accepted solution]

See the attached qvw.

I just saw your comment about needing to do not 3, but 240.  That complicates it.

[m_woolf]

The request to do the previous 240 days doesn't make sense. If the average daily sales was 7, the Result would be a 6 followed by 195 zeros.

[vishalj88]

The solution you provided with peek was similar to one i was thinking. I understand your concern with 240 days and the number of zeros. In our problem we're dealing with metrics that are in small decimals(usually in 1/100ths) so we won't be running into that issue. I simplified this problem for the purposes of a POC. 

Even if we're able to show how to do this for 30 days, I would consider that a win since I can work on scaling it up.