topic Re: Muliply Previous Rows in QlikView

Muliply Previous Rows

vishalj88 — Sat, 16 Nov 2024 19:17:49 GMT

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	543
A	4-Jan	6	120	654
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	632
B	4-Jan	10	180	1063

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

Re: Muliply Previous Rows

m_woolf — Wed, 15 Jan 2020 18:06:24 GMT

Take a look at peek

Re: Muliply Previous Rows

vishalj88 — Wed, 15 Jan 2020 18:09:47 GMT

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

Re: Muliply Previous Rows

m_woolf — Wed, 15 Jan 2020 19:27:20 GMT

See the attached qvw.

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

Re: Muliply Previous Rows

m_woolf — Wed, 15 Jan 2020 19:34:21 GMT

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.

Re: Muliply Previous Rows

vishalj88 — Wed, 15 Jan 2020 21:28:18 GMT

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.