Set Analysis and Dollar Sign Expansion

[newuser]

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

[vinieme12]

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

[vinieme12]

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

[newuser]

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.

[marcus_sommer]

{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

[vinieme12]

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

Or 

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

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

[newuser]

@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)} 

[newuser]

@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)} 

[vinieme12]

 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
];

[marcus_sommer]

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