Hi
I need to show product levels in a pivot table something like the table below
outside of the table user can choose Levels from L0-L8
so for each level I need see if they are in the level so i have something like this below
if (dimension()=0 and , sum({<time={'$(=$(=vWalletMaxYear))'}, Level={"L0"}>} size),
if (dimension()=1 and , sum({<time={'$(=$(=vWalletMaxYear))'}, Level={"L1"}>} size),
if (dimension()=2 and , sum({<time={'$(=$(=vWalletMaxYear))'}, Level={"L2"}>} size),.......
if (dimension()=8 and , sum({<time={'$(=$(=vWalletMaxYear))'}, Level={"L8"}>} size)))))))))
but then i realized if someone skipped a level
example, if it is selected Level0,1,2, but skipped 3, 4 and also selected L5, the above will be wrong, all levels will show 0 after 2.
the above will be wrong. so for each dimension, i need to check if Product level is the same level as dimension other wise go to next level, so there are many ifs inside, in each dimension i need to add checks as below
if (dimension()=1 and and SubStringCount('|' & Concat(distinct [Product Levels], '|') & '|', '|L1|'), sum({<time= {'$(=$(=vWalletMaxYear))'}, Level={"L1"}>} size),
if ( SubStringCount('|' & Concat(distinct [Product Levels], '|') & '|', '|L2|'), sum({<time= {'$(=$(=vWalletMaxYear))'}, Level={"L2"}>} size),
....
if ( SubStringCount('|' & Concat(distinct [Product Levels], '|') & '|', '|L8|'), sum({<time= {'$(=$(=vWalletMaxYear))'}, Level={"L8"}>} size),
anyway to optimize this as it is very slow in mashup (we have a lot of data)?
| total |
|
|
|
size |
revenue |
Prior year size |
revenue |
| |
level1 ProductA |
|
|
100 |
|
|
|
| |
|
level2 prodA |
|
100 |
|
|
|
| |
|
|
level3 ProdA |
99 |
|
|
|
| |
level1 ProductB |
|
|
50 |
|
|
|
| |
|
level2 prodB |
|
45 |
|
|
|
