Crosstable

[#1]

I it possible to pivot my data (by script) as the following example? On the left is the data, in the blue box is the desired format.

[rbecher]

Hi,

it's not just pivoting (or transposing) - it's also an aggregation.

I would do a script loop over the year and aggregate the fields YR-1, YR-2 and YR-3 with

LOAD ID
, <the_loop_year> as YEAR
, sum(<Field>) as Amount, sum(if(YEAR=<the_loop_year>, Amount, Null()) as Amount
, sum(<Field>) as Amount, sum(if(YEAR=<the_loop_year> -1, Amount, Null()) as Amount_YR-1
, sum(<Field>) as Amount, sum(if(YEAR=<the_loop_year> -2, Amount, Null()) as Amount_YR-2
, sum(<Field>) as Amount, sum(if(YEAR=<the_loop_year> -3, Amount, Null()) as Amount_YR-3
from...
group by ID
where YEAR >= <the_loop_year> and YEAR <= <the_loop_year> -3;

if this is a fixed amount of years.

- Raf

[#3]

Suppose i don''t have multiple values for each year so i dont need the aggregation. What is the best way to pivot/transpose the values?? is there a scipt function to do this??

[rbecher · Accepted solution]

then you could use above function in chart, see attachment

- Ralf