SET Analysis issue

[#1]

Hey all,

I have an issue with a SET analysis. I defined following user defined function: udfTest = $1 & '_' & Text($2) & '_' & $3;

Using this in a text box works for me: =$(udfTest('Text', Year*100 + 1, 'Text')) results in Text_201501_Text. Well.

But now I would like to filter some data with the SET analysis but somehow it doesn't work

=sum({$<Field1={$(udfTest('Text', Year*100 + 1, 'Text'))}>Field2}

I already tried several things with '', "", [] and so on but I can't figure out the mistake. Anyone an idea?

Thanks for any reply!

BR

Henrik

[CELAMBARASAN]

Try with the Equal sign

=sum({$<Field1={$(=udfTest('Text', Year*100 + 1, 'Text'))}>}Field2)

[PrashantSangle]

Hi,

use "" and =

sum({$<Field1={"$(=udfTest('Text', Year*100 + 1, 'Text'))"}>}Field2)

Regards

[rubenmarin]

Hi Henrik, check if that works when you select only one Year.

Set analisys and $-expansion is calculated using document selections, before the table is calculated, so it no uses the dimension values to change the Year dinamically.

[#5]

Hey all,

thanks for those fast replys! amazing!

But those fixes both don't work for me. The good part is, that there is no more error, but it just returns 0. And nothing changes if I replace the field to look for. So I assume it accepts the dollar sign expression, but somehow it doesn't find the correct value. The sum to calculate should be 1, not 0.

The aspect of the Year thing doensn't change anythang as well. I tried with a fixed number of 2015 instead of the Year field. But also no change at all.

And as an info, if I enter the string manually into the SET expression, it works.

=Sum({$<Field1={Text_201501_Text}>}Field2)

AND

If I try this in a text box: =$(=udfTest('Text', Year*100 + 1, 'Text') the result is -. ....

[PrashantSangle]

Hi,

In your chart where you write this expression dont give any label to this expression.

then check what value it is evaluating for

$(=udfTest('Text', Year*100 + 1, 'Text'))

If possible take screen shot of it and post it.

Regards

[rubenmarin · Accepted solution]

And this?:

sum({$<Field1={$(=Chr(39)& $(udfTest('Text', Year*100 + 1, 'Text')) & Chr(39))}>}Field2)

A sample will be nice to make some tests.

[#8]

Wow! That works as expected. Very weird that we need to use Chr(39) aka ' to make this happen. But well thanks. It runs.

[rubenmarin]

Yes, it's needed to avoid messing with the ' used to pass parameters to udfTest, the difference:

sum({$<Field1={$(=Chr(39)& $(udfTest('Text', Year*100 + 1, 'Text')) & Chr(39))}>}Field2)

sum({$<Field1={$(='$(udfTest('Text', Year*100 + 1, 'Text'))')}>}Field2)

The bolded text is the part between the opener ' and the closer ', you can see the second option can't be procesed, one way to avoid this is the use of Chr(39).