# App Development

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for
Did you mean:
Contributor III

## Set Analysis String Concatenation

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 ?

Labels (3)

• ### String

1 Solution

Accepted Solutions
MVP

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)``

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.

3 Replies
MVP

thanks

Marco

Contributor III
Author

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

MVP

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)``

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.