topic Re: Set Analysis and Dollar Sign Expansion in App Development

Set Analysis and Dollar Sign Expansion

newuser — Tue, 17 May 2022 23:02:08 GMT

Hi - I've searched the community on this question but am still a bit unclear. I know dollar sign expansion is used as a replacement value, especially when you have a measure or a function, but in the example below, I'm using all fields from the load script, no functions or variables. Can someone explain why the 2nd option below works? The only difference is, I'm using dollar sign expansion.

Doesn't work (no syntax errors, but result incorrect/too low):

sum({1 <Month_Placed ={"<=$(=CurrMonth)"}, TranYr = {PrevYr} >}
if(placedt_all=aggr( nodistinct max({1} placedt_all),acctpol), Placed))

Works (more reasonable end result):

sum({1 <Month_Placed ={"<=$(=CurrMonth)"}, TranYr = {$(=PrevYr)} >}
if(placedt_all=aggr( nodistinct max({1} placedt_all),acctpol), Placed))

Re: Set Analysis and Dollar Sign Expansion

vinieme12 — Wed, 18 May 2022 05:58:28 GMT

Refer these blogs,

https://community.qlik.com/qlik-blogpost/3406

https://community.qlik.com/t5/Qlik-Design-Blog/The-Little-Equals-Sign/ba-p/1475639

https://community.qlik.com/t5/Qlik-Design-Blog/The-Magic-of-Dollar-Expansions/ba-p/1471979

Re: Set Analysis and Dollar Sign Expansion

vinieme12 — Wed, 18 May 2022 06:01:13 GMT

If your variable is defined with and = sign or not also makes a difference

You can also look at the expression editor to see how your variables are being evaluated

Re: Set Analysis and Dollar Sign Expansion

newuser — Wed, 18 May 2022 13:43:03 GMT

What I'm asking about is NOT variables but fields. Those blogs talk about variables. My question is, why does a dollar sign expansion make a difference for FIELDS? THank you.

Re: Set Analysis and Dollar Sign Expansion

marcus_sommer — Wed, 18 May 2022 15:21:05 GMT

{PrevYr} isn't referencing to a field else it's treated as a string like: {'PrevYr'} and {$(=PrevYr)} evaluates the content of the $-sign expansion at first which means in this case: only(PrevYr) and used then this result.

Beside this you couldn't compare a field against a field within a real set analysis because it worked like a selection on a column-level and not on a row-level. You may use something like: ... FieldX = {"=FieldA=FieldB"} ... but it's just an if-loop with another syntax.

- Marcus

Re: Set Analysis and Dollar Sign Expansion

vinieme12 — Wed, 18 May 2022 15:23:04 GMT

So PrevYr is a field??

You absolutely don't need to $ expand a field, why are you even trying this?

Again the same thing applies, you can see how your expression evaluates in the expression editor when you $wrap a field

If you want to do a row level comparison try as below

sum({1 <Month_Placed ={"<=$(=CurrMonth)"}, TranYr = {"=TranYr=PrevYr"} >}

if(placedt_all=aggr( nodistinct max({1} placedt_all),acctpol), Placed))

sum({1 <Month_Placed ={"<=$(=CurrMonth)"} >}

if(placedt_all=aggr( nodistinct max({1} placedt_all),acctpol) And TranYr=PrevYr , Placed))

Re: Set Analysis and Dollar Sign Expansion

newuser — Wed, 18 May 2022 17:36:32 GMT

@marcus_sommer , then I think you're saying my dollar sign expansion below is "ok" ? Or is the best practice to use the second method you mentioned, i.e., FieldX = {"=FieldA=FieldB"}? Either way, I seem to be getting the same result. Trying to think in what situations would the result be different.

TranYr = {$(=PrevYr)}

Re: Set Analysis and Dollar Sign Expansion

newuser — Wed, 18 May 2022 17:35:32 GMT

@vinieme12 , I seem to be getting the same result whether I use the syntax below or the suggested one above, i.e., TranYr = {"=TranYr=PrevYr"} . Can you give me a hypothetical example or scenario where the syntax below would NOT work?

TranYr = {$(=PrevYr)}

Re: Set Analysis and Dollar Sign Expansion

vinieme12 — Thu, 19 May 2022 01:41:43 GMT

TranYr = {$(=PrevYr)}

$(=FieldName) can only return a Single value; So if there are different values in Field it will return null

Correct way to avoid this is to use an aggregation function

TranYr = {"$(=Max(PrevYr))"}

Try $(=PrevYr) on below data

raw:
Load * Inline [
ID,TranYr,PrevYr
1,2021,2021
2,2022,2021
3,2021,2021
4,2022,2021
5,2021,2021
6,2000,1999
];

Re: Set Analysis and Dollar Sign Expansion

marcus_sommer — Thu, 19 May 2022 06:03:30 GMT

It depends on data, data-model and UI requirements if applying if-loops or statements like {"=FieldA=FieldB"} are suitable or not. In general there are better approaches possible, for example making such checks already within the script in a flag-field and then querying this flag in the UI or creating a data-model which has only a single year-field.

Not each part of the requirements might be solvable in an optimized way - at least not all together ... Sometimes the second/third best approach is practically enough. It depends ...

- Marcus