Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Set Analysis String Concatenation

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

abonnery

Contributor III

2022-01-04
11:28 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

821 Views

1 Solution

Accepted Solutions

MarcoWedel

MVP

2022-01-05
08:25 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3 Replies

MarcoWedel

MVP

2022-01-04
11:54 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

please post some sample data and your expected result

thanks

Marco

786 Views

abonnery

Contributor III

2022-01-05
05:14 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

726 Views

MarcoWedel

MVP

2022-01-05
08:25 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.