Set Analysis using text variable

[#1]

Hi,

I have a dataset with states defined as 2 letter codes (CA, GA, FL, etc). We run experiments in states over specific time periods and I'm building a set of charts where we'd like to compare results between 2 different sets of states during specific time periods. After lots of struggle, I figured out how to build a set analysis to control the date ranges input with variables.

This set expression works:

Count({<state = {'CA', 'GA'}>} ID)

I've set up a variable called vControlStates and I'd like to assume that users input state lists like: CA,GA,FL

If a user enters the data like this: CA', 'GA', 'FL then this statement works:

Count({<state = {'$(vControlStates)'}>} ID)

That's a painful syntax for users, so I tried a Replace to see if that would work:

Count({<state = {'=Replace(vControlStates,",","', '")'}>} ID)

but the answer is always 0. I know I'm missing something simple - HELP!

BTW, two comments to Qliktech if anyone from there is watching:

1. the Set syntax is wonderfully powerful and excruciatingly painful to figure out. When will you do REAL documentation of the syntax with useful examples for real people?

2. It would be great to have an "expression evaluator" in the Expressions dialogue so you could see in detail how the expression is being expanded.

[johnw · Accepted solution]

I believe this should work:

count({<state={'$(=replace(vControlStates,',',chr(39)&','&chr(39)))'}>} ID)

1. I agree that set analysis is both wonderfully powerful and excruciatingly painful to figure out. I assume you at least found the help text for it? I consider it at least decent documentation and decent examples. I'm sure it could be significantly improved, particularly if they added as examples a lot of the types of things that routinely come up on the forum.

2. Well, this isn't exactly the same thing as you're asking for, but might be helpful. Create a straight table with no caption, and use your set analysis expression as the FIRST expression in the chart. The caption of the chart should now be the unevaluated expression, while the column heading will show a partially-evaluated expression where all the dollar-sign expansion has already been done. We typically use a lot of dollar sign expansion in complicated set analysis, and this often gives a quick clue as to what is going wrong. For intance, if you do it with the expression above, you should be able to see if what I wrote really IS giving you the list of states in single quotes, or if it's still not quite right.

[#3]

John, that expression almost worked. What I ended up with was:

Sum({<state={'$(=replace(vControlStates,',',chr(39)&','&chr(39)))'}>} ID)

You missed a paren I think.

Thanks - the application I did with this is really cool.

[johnw]

mbrandemuehl wrote:You missed a paren I think.

Whoops, yep. Now fixed in the original post.