Set analysis syntax query

[#1]

I hope someone can help me to understand some expressions written by an ex-colleague that I'm being asked to modify. I've looked at Sholcomb's posting 'Set Analysis Filter Based on Variable Table at http://community.qlik.com/forums/p/24521/93659.aspx#93659 and John's helpful response that very clearly explains his example. Unfortunately my expressions are (or appear to my inexperienced eye to be) slightly different, and I'm not familiar enough with the data in the application to be confident that my understanding is correct.

My first expression is sum({$ <ASS_BATCH_ID={$(=ASS_BATCH_ID_REF)}> } CAP_FTE)

The expressions sums the values of the field CAP_FTE, and I believe that <ASS_BATCH_ID={$(=ASS_BATCH_ID_REF)}> is a set modifier that overrides the value in the field ASS_BATCH_ID. If $(=ASS_BATCH_ID_REF) is a dollar sign expansion this suggests to me that the ASS_BATCH_ID field is being overridden with the value of the ASS_BATCH_ID_REF field.

However, I'm confused by the two $ in the expression, as I know that this symbol can also represent "records of the current selection".

I initially translated the expression as "sum the CAP_FTE values where the ASS_BATCH_ID value of the currently selected records is equal to the value of the field ASS_BATCH_ID_REF". However, this doesn't tie up with the data that is being returned, in that the data that is being queried has one set of values in the ASS_BATCH_ID field (all even numbers), and another in the ASS_BATCH_ID_REF field (all odd numbers). I'm sure I'm missing something simple here?

The second expression is very similar, sum({$ <ASS_BATCH_ID={$(=SSS_BATCH_ID_REF)}, CAP_MTHC={[NA]}>} RSF_SCE_FTE)

Here we are doing something very similar, including overriding the CAP_MTHC field with the value of NA. My question here is what are the [square brackets] doing in the expression?

I'd be very grateful to anyone who could help me determine exactly what is going on here!

(Using Desktop v9.0 7320.7 SR2)

[johnw · Accepted solution]

The first $ in the expression is optional. It says that the modifications we're making are going to start with the CURRENT set of data. The $ indicates the currently-selected set of data, like a 1 indicates the set of ALL data. Since the currently-selected set of data is the default starting point for set analysis, you don't have to explicitly state it with the first $. I leave that $ off, partially because it is easy to confuse with dollar sign expansion, which is a completely different thing.

So I read it about like you, "start with the currently-selected data, then instead of using the ASS_BATCH_ID selection (if any), replace it with a 'selection' of ASS_BATCH_ID_REF, then for that data set, sum all CAP_FTE". I cannot explain how you would get any results if ASS_BATCH_ID is all even numbers, and ASS_BATCH_ID_REF is all odd numbers.

I don't know what the square brackets are doing in the second expression. But I tried it out, and apparently QlikView is happy to interpret them the exact same way as single quotes in this case. I wouldn't use them. I'd use single quotes. I try to put literals in single quotes, field names in double quotes, and table names in brackets. QlikView isn't so strict, but I like having some sort of standards so that I can recognize at a glance what is what.

[#3]

Thanks very much for your help John.

I'm pleased that my original interpretation was correct, as I was reasonably confident I had understood set analysis before coming across this! I am now comfortable enough with the expression to start poking around in the data to resolve why I'm getting the results that I am.

Like you, I intend to adopt the single quotes/double qoutes/square bracket standards, and knowing that square brackets are interpreted in the same way as single quotes will help me when I have to modify expressions that I've inherited from predecessors.

Thanks again,

Sophie