Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to build a pivot table where the column and row will be expression. I use the Pick method to create the table. But I have a problem when I have 2 dimensions. When I collapse the first dimension, the row will disappear (due to row value = null). How can I add an expression for the first dimension? It is do-able if I use the Nested If, but the expression will be very messy. I hope there is a way with the Pick Method.
Please refer to the attached QVW for the example between IF vs Pick.
*This is a simplified version of the actual pivot.
*Unchecking the "suppress zero" value is not an option. (I need to suppress null value in DimB)
*This table have multiple expression columns. (not included in this example)
May be use this with pick
=Sum(Aggr(Pick(DimA,
Pick(DimB, 1, 2, 3),
Pick(DimB, 4, 5, 6),
Pick(DimB, 7, 8, 9)
), DimA, DimB))
Hi,
if I'm not mistaken, then eclosing the pick functions with an aggregation like Sum() should do the trick.
For your example:
Sum(
Pick(DimA,
Pick(DimB, 1, 2, 3),
Pick(DimB, 4, 5, 6),
Pick(DimB, 7, 8, 9)
)
)
Regards
May be use this with pick
=Sum(Aggr(Pick(DimA,
Pick(DimB, 1, 2, 3),
Pick(DimB, 4, 5, 6),
Pick(DimB, 7, 8, 9)
), DimA, DimB))