Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to create a report composer where the users can create pivot tables by their own choosing which dimensions and expressions show in the table.
Everything seems to work fine until I need to show the row relative values (%).
Absolute:
dim1 | dim2 | exp1 | exp2 | total |
x | a | 10 | 90 | 100 |
b | 30 | 30 | 60 |
Relative:
dim1 | dim2 | exp1 | exp2 | total |
x | a | 10% | 90% | 100% |
b | 50% | 50% | 100% |
= Count(DISTINCT my_value)
/
Count( TOTAL <dim1, dim2> DISTINCT my_value)
In this case i can easily define which are my dimensional scopes (dim1 and dim2) to obtain the correct relative value. But what about if user needs to add dim3? This change my formula to:
= Count(DISTINCT my_value)
/
Count( TOTAL <dim1, dim2, dim3> DISTINCT my_value)
Is it possible to do it dynamically?
I tried something like:
= Count(DISTINCT my_value)
/
Count( TOTAL <GetFieldSelections(dim_selected)> DISTINCT my_value)
or using variables to get the selected fields or, even, converting total to set analysis but without any success.
Can somebody help me please?
Thanks in advance,
Emanuele
Hi Emanuel, I think you attempt was close, can you try it using a $-expansion syntax?:
= Count(DISTINCT my_value)
/
Count( TOTAL <$(=GetFieldSelections(dim_selected))> DISTINCT my_value)
Or using concat:
= Count(DISTINCT my_value)
/
Count( TOTAL <$(=Concat(dim_selected,','))> DISTINCT my_value)
Hi Emanuel, I think you attempt was close, can you try it using a $-expansion syntax?:
= Count(DISTINCT my_value)
/
Count( TOTAL <$(=GetFieldSelections(dim_selected))> DISTINCT my_value)
Or using concat:
= Count(DISTINCT my_value)
/
Count( TOTAL <$(=Concat(dim_selected,','))> DISTINCT my_value)
Hi Ruben,
first solution works properly! Thank you very much!