Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
vishalj88
Contributor II
Contributor II

Muliply Previous Rows

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.

 

ProductDateSalesReturn (Expected Result)Comment
A1-Jan3-Blank because did not accumulate 3 days of historical data
A2-Jan4-Blank because did not accumulate 3 days of historical data
A3-Jan5605*4*3
A4-Jan61206*5*4
B1-Jan2-Blank because did not accumulate 3 days of historical data
B2-Jan3-Blank because did not accumulate 3 days of historical data
B3-Jan6366*3*2
B4-Jan1018010*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

1 Solution

Accepted Solutions
m_woolf
Master II
Master II

See the attached qvw.

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

View solution in original post

5 Replies
m_woolf
Master II
Master II

Take a look at peek

vishalj88
Contributor II
Contributor II
Author

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

m_woolf
Master II
Master II

See the attached qvw.

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

m_woolf
Master II
Master II

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
Contributor II
Contributor II
Author

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.