Set Analysis String Concatenation

[abonnery]

Dear Qlik Experts, 

I am trying to do a set analysis with a string concatenation with this formula : 

sum({$<[Cost Baseline Version - FA]={$("RF_"& $(=Num([Year - FA]))&"01")}>} [EAC - FA]/1000000)

I must have the wrong formulation because the criteria isn't recognized and doesn't provide any numbers. 

The formula below works.

'RF_' & Num([Year - FA]+1) &'_01'

 Do you see why my set analysis isn't working ? 

Thank you for your help

[MarcoWedel]

please post some sample data and your expected result

thanks

Marco

[abonnery]

Here is a data sample: 

I am trying to make the set analysis on the right work (I know there is an additionnal bracket in the picture it's just to show you the results I could like to see).

The figures highlighted in red should not be null if the set analysis was working. For each year, the set analysis should do the sum of all [EAC-FA] of the [Cost Baseline Version - FA] of January of the same year.

For now I have tested a few different script without success: 

sum({<[Cost Baseline Version - FA]={"$(='RF_'& Num([Year - FA]+1) &'_01')"}>} [EAC - FA]/1000000)

sum({$<[Cost Baseline Version - FA]={$("RF_"& $(=Num([Year - FA]))&"01")}>} [EAC - FA]/1000000)

I believe the criterion in bold is not recognized but I cannot find how to make it work

[MarcoWedel · Accepted solution]

Dollar sign expansions and set expressions are only evaluated once, i.e. not on row level, so this cannot work by design.

$(=Num([Year - FA])) for example is evaluated once before the rows are calculated. As there are at least three different [Year - FA] values possible in the current selection state of your example, it will not return a numerical value as you expect. 

If you have a unique ID field in your fact table, then you could use something like

Sum({$<ID={"=[Cost Baseline Version - FA]='RF_'&[Year - FA]&'_01'"}>} [EAC - FA]/1000000)

instead.

Using only the fields of your example, this should also work

Sum(If([Cost Baseline Version - FA]='RF_'&[Year - FA]&'_01', [EAC - FA]/1000000))

although I think a set expression solution should be preferred due to better performance.