Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have data in the following form and would like to create a straight table which has the same fields (Code, Date and balance) but the balance field should have accumulated values (chronologically) .
Code | Date | Balance |
R1720 | 31-Aug-12 | 20,971 |
R1720 | 03-Sep-12 | 12,994 |
R1720 | 04-Sep-12 | -12,807 |
R1720 | 05-Sep-12 | 1,366 |
R1720 | 06-Sep-12 | -15,303 |
R1720 | 10-Sep-12 | 2,931 |
R1720 | 18-Sep-12 | -12,269 |
R1720 | 28-Sep-12 | 6,380 |
R1720 | 01-Oct-12 | 47 |
R1720 | 04-Oct-12 | 1,701 |
The straight table should look something like this. Please note the last row sums all the values because the date field is the highest.
Code | Date | Balance |
R1720 | 31-Aug-12 | 20,971 |
R1720 | 03-Sep-12 | 33,965 |
R1720 | 04-Sep-12 | 21,158 |
R1720 | 05-Sep-12 | 22,524 |
R1720 | 06-Sep-12 | 7,221 |
R1720 | 10-Sep-12 | 10,152 |
R1720 | 18-Sep-12 | -2,117 |
R1720 | 28-Sep-12 | 4,263 |
R1720 | 01-Oct-12 | 4,310 |
R1720 | 04-Oct-12 | 6,011 |
Would appreciate all the help i can get
Regards
Faisal
In that case the load script version will not restart the cumulative sum on a new Code. The expression will restart the cumulative sum for each order. See updated attached qvw
I could make another a data island with the same data and linking it to the oringal through the code field. That way i could compare the date fields. However is there another way i could do this? like a simplier way of doing this?
Hi,
Use the Above() function. Something like =Above(Column(3))+sum(Balance)
You may need to do it in two different columns, one with the row value and another with the accumulated value.
Jonathan
There's an issue with that because i will be hiding rows where the balance field is <=0 but those values need to be incorporated in the sum.
See the attached qvw. One table is build up in the load script. The other uses an expression
Gwassenaar,
What if there multiple codes?
In that case the load script version will not restart the cumulative sum on a new Code. The expression will restart the cumulative sum for each order. See updated attached qvw