Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
abonnery
Contributor III
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 ? 

Thank you for your help

Labels (3)
1 Solution

Accepted Solutions
MarcoWedel

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.

View solution in original post

3 Replies
MarcoWedel

please post some sample data and your expected result

thanks

Marco

abonnery
Contributor III
Contributor III
Author

Here is a data sample: 

SA baseline.PNG

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

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.