Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
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?