Indirect Set analysis with data islands using concat

[#1]

Hello,

after a lot of search and trial and error - i have 2 tables that are not joined, and have used indirect set analysis to solve get the selections

but i am not able to get the desired result if i use this expression :

SET vNonUsageSummaryRevenue1 =  Sum({$<[NUSG DEST INV CODE]={"$(=concat(distinct [USG INV CODE],"','"))"}>}[NUSG FIN CHG AMT SUM]);

i want to sum the field [NUSG FIN CHG AMT SUM] where the [NUSG DEST INV CODE] = [USG INV CODE]

the [NUSG DEST INV CODE]  and [NUSG FIN CHG AMT SUM] are fields in one table that is not joined to the other table where the field is [USG INV CODE]

i only always get 0 in vNonUsageSummaryRevenue1

if i use the concat function in a text box, i can see that the USG INV CODE is selected correctly for 1 value, but if multiple USG INV CODE are selected there is no value in the text box, it show "-".

and if i create the concat as a variable,  there is nothing in the resultant variable.

Set vUsgInv = chr(39) & concat(distinct [USG INV CODE],chr(39) & chr(44) & chr(39)) & chr(39);

SET vNonUsageSummaryRevenue2 =  Sum({$<[NUSG DEST INV CODE]={$(=$(vUsgInv))}>}[NUSG FIN CHG AMT SUM]);

the vNonUsageSummaryRevenue2 is always "-" no matter what is selected,

but if i put the vUsgInv in a text box, i can see the correct selections.

i am unable to find the problem here in the concat statement.

can someone please help me?

[swuehl]

One problem might be the format of your delimiter string, I think you are using double quotes enclosing single quotes in your first expression used in the SET vNonUsageSummaryRevenue1. Try removing the double quotes, a string using single quotes around the comma should be enough.

I assume your CODE is a number or literals, but not a date or a dual or something thats need special formatting.

If so, you don't need to use quotes for the list of values in your set expression element set.

Looking at your second approach, I also think that formatting the vUsgInv in a way enclosing the CODEs with quotes is causing the problem here. At least while trying to reproduce your issue, this is what I've seen using QV11.

[#3]

Hello Swuehl,

thank you for the reply.

the codes are not dates, they are numbers like 1821, 1822 etc.

but they might be loaded in as string,

just ot make sure, that my expression was correct i put this in a text box,

=Sum({$<[NUSG DEST INV CODE]={'1821','1822'}>}[NUSG FIN CHG AMT SUM])

the text box results show  the correct SUM amount.

i tried removing the '' as well.and it too worked fine.

=Sum({$<[NUSG DEST INV CODE]={1821,1822}>}[NUSG FIN CHG AMT SUM])

so i am assuming the concat is what is causing the problem.. but not able to figure out what is wrong here.

[swuehl]

Leo,

I think your variable definition could look like this:

=  Sum({$<[NUSG DEST INV CODE]={$(=concat(distinct [USG INV CODE],','))}>}[NUSG FIN CHG AMT SUM])

i.e. removing the double quotes (two pairs).

You could put above in a variable definition in variable overview and check.

If you want to define the variable in the script, you may need to take care of the dollar sign expansion (you can't expand and evaluate the concat expression in the script).

You could do it like:

Set v2 = Sum({$<[NUSG DEST INV CODE]={@(=concat(distinct [USG INV CODE],','))}>}[NUSG FIN CHG AMT SUM]);

Let v3 = '='&Replace(v2,'@','$');

and then use v3 in your chart expression.

Hope this helps,

Stefan