Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
See the attached qvw.
I just saw your comment about needing to do not 3, but 240. That complicates it.
Take a look at peek
Yes, I'm aware of Peek. How would i write such a function so i'm getting answer i'm looking?
See the attached qvw.
I just saw your comment about needing to do not 3, but 240. That complicates it.
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.
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.