Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I noticed that the option Suppress When Value Is Null apperently breaks Expression with Total <Dimension>.
Example data:
OBJECT | LEVEL1 | LEVEL2 | KPI | YEAR |
A | A | 30 | 2011 | |
B | A | B | 15 | 2011 |
C | A | C | 15 | 2011 |
D | D | 40 | 2011 | |
E | D | E | 15 | 2011 |
F | D | F | 10 | 2011 |
G | D | G | 15 | 2011 |
A | A | 42 | 2012 | |
B | A | B | 16 | 2012 |
C | A | C | 26 | 2012 |
D | D | 52 | 2012 | |
E | D | E | 20 | 2012 |
F | D | F | 16 | 2012 |
G | D | G | 16 | 2012 |
When a single value of LEVEL1 is selected, I need to display all associated LEVEL2 values, combined with the YEAR and the KPI value (MAX, MIN, doesn't matter). So far, so simple.
Now I need a second expression column that shows the the associated KPI value of the LEVEL1 OBJECT in the respective YEAR. I can do that using a Set expression an the Total Qualifier: =MAX({1<OBJECT={"$(=ONLY(LEVEL1))"}>} TOTAL <YEAR> KPI)
Again, simple.
Trouble ahead: Currently, the rows with null-Values of the LEVEL2 are dispalyed, which is not wanted. I can suppress them via the option named in the title ("Suppress When Value Is Null"). However, then, the expression breaks. Why? Any Ideas?
See my uploaded workbook as an example.
Thanks!