
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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
- Tags:
- qlikview_scripting
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
See the attached qvw.
I just saw your comment about needing to do not 3, but 240. That complicates it.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Take a look at peek

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, I'm aware of Peek. How would i write such a function so i'm getting answer i'm looking?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
See the attached qvw.
I just saw your comment about needing to do not 3, but 240. That complicates it.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
