List multiple ranked values in a straight table

[Adam_Smith]

Hi,

Is it possible to create a straight/pivot table in the following format?

Employee	#	Stage1	Stage2	Stage3
Joe	1	ABC	JKL	BCD
	2	DEF	MNO	EFG
	3	GHI	PQR
	4		STU
	5		VWX
	6		YZA
Total		3	6	2

- Employee is the dimension

- # is the rank within each expression

- Stage 1, 2, 3 are separate expressions, using set analysis to determine which values show up within each stage

The idea is that the table is "Auto Expanding" depending on the number of possible values within each stage. (i.e. currently 6 rows, but could expand to 10 or 12 rows for other employees depending on number of possible values)

[Or]

You probably could get something similar.

Dimension - Employee

Dimension - ValueLoop(1,12,1)

Measure1 - Max(Stage1,ValueLoop(1,12,1))

Measure2 - Measure1 - Max(Stage2,ValueLoop(1,12,1))

etc.

And set it to hide zero/null lines.

[Adam_Smith]

Hi OR,

Thanks for the response.

I have tried as you have suggested, however I cannot get the expression to iterate through each list of values in the valueloop. Instead, I just get the max value against each dimension (e.g. below).

The expression I have used is;

=max(Stage1,Valueloop(1,12,1))

Employee	Valueloop(1,12,1)	Stage1	Stage2	Stage3
Joe	1	ABC	JKL	BCD
	2	ABC	JKL	BCD
	3	ABC	JKL	BCD
	4	ABC	JKL	BCD
	5	ABC	JKL	BCD
	6	ABC	JKL	BCD

Do I need to use an if-statement to get it to refer to the each valueloop dimension?

if(valueloop(1,12,1) = 1, max(Stage1,1),

if(valueloop(1,12,1) = 2, max(Stage1,2))) etc...

Or is there a more efficient way to write the expression so it automatically iterates through each value in the valueloop?