Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have a field that has a value in period 0 that is the begining amount of 100. Some of the following periods either add or subract from this and have values like -4, or 7. How do I get the value desired below given the table below:
Year | Period | Values in Field | Value Desired |
2011 | 0 | 100 | 100 |
2011 | 1 | 0 | 100 |
2011 | 2 | 3 | 103 |
2011 | 3 | 0 | 103 |
2011 | 4 | 0 | 103 |
2011 | 5 | -4 | 99 |
2011 | 6 | 7 | 106 |
2011 | 7 | 0 | 106 |
2011 | 8 | 0 | 106 |
2011 | 9 | 5 | 111 |
2011 | 10 | 0 | 111 |
2011 | 11 | 2 | 113 |
2011 | 12 | -1 | 112 |
2012 | 1 | 0 | 112 |
2012 | 2 | 0 | 112 |
2012 | 3 | 0 | 112 |
i.e. Can I do this with set analysis or in the script loading?
Well, if you can do it in the script, do it in the script, maybe using something like:
TEST:
LOAD *, rangesum([Values in Field],peek([ValueDesired])) as ValueDesired;
LOAD Year,
Period,
[Values in Field],
[Value Desired]
FROM
[http://community.qlik.com/thread/52426?tstart=0]
(html, codepage is 1252, embedded labels, table is @1);
Or create a straight table chart with dimensions Year and Period and as expression:
=rangesum(above(total [Values in Field],0,RowNo(total )))