Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I wan to calculate the sum of past 2 rows + current row + sum of next 2 rows while loading itself.
For eg,
Actual Table:
Date | Production |
---|---|
01/10/2013 | 20 |
02/10/2013 | 15 |
03/10/2013 | 10 |
04/10/2013 | 23 |
05/10/2013 | 65 |
06/10/2013 | 0 |
07/10/2013 | 2 |
Eg: Load Date, Production, ( Calculation = Past 2 values + current value + next 2 values) as Expected Result;
SQL Select * from Actual Table;
Expected Load data:
Date | Production | Expected Result = Past 2 values + current value + next 2 values |
---|---|---|
01/10/2013 | 20 | 20 + 15 + 10 = 45 |
02/10/2013 | 15 | 20 + 15 + 10 + 23 = 68 |
03/10/2013 | 10 | 20 + 15 + 10 + 23 + 65 = 133 |
04/10/2013 | 23 | 15 + 10 + 23 + 65 + 0 = 113 |
05/10/2013 | 65 | 10 + 23 + 65 + 0 + 2 = 100 |
06/10/2013 | 0 | 23 + 65 + 0 + 2 = 90 |
07/10/2013 | 2 | 65 + 0 + 2 = 67 |
Where Green - Past values,
Purple - Current Value
Blue - Next Values and
Red - Final Result
I hope this is enough to understand and give answer for my question.
Please give your answers. I am awaiting your response.
Did you check my earlier attachment? I thought it was working.
Maybe like this:
Set DateFormat = 'DD/MM/YYYY';
INPUT:
LOAD Date,
Production
FROM
[http://community.qlik.com/thread/98253]
(html, codepage is 1252, embedded labels, table is @1);
TMP:
LOAD *,
rangesum(peek(Production),Peek(Production,-2),Production) as TMPSum
Resident INPUT ORDER BY Date desc;
RESULT:
LOAD Date,
Production,
rangesum(Peek(Production,-2),Peek(Production),TMPSum) as Sum
Resident TMP ORDER BY Date asc;
drop table INPUT;
drop table TMP;
May be like attached sample?
Or if you want a chart expression try a straight table or pivot table with Date and Production as dimensions and as expression: rangesum(Above(total sum(Production),0,3),Below(total sum(Production),1,2))
I did something as same as you said. But while zooming I am not getting the actual values. It varies depending upon the selection. So I want to do it while loading values. If we can fix this zooming with your solution, Please let me know...
If you don't want selections to influence the values then you can't use a chart expression, but you need to calculate the values in the script like Stefan showed above.
Did you check my earlier attachment? I thought it was working.
Yes, its really useful for me to fix my issue. Thanks