topic Using Field names as values in set analysis in QlikView

Using Field names as values in set analysis

Anonymous — Wed, 09 Dec 2009 20:34:36 GMT

I need to replace the ("1*"} in the example below with a field in the Qlikview model.

sum({$<[ACCOUNT Code] = {"1*"}>} [Amount])

However if I use the field in this space the answer always comes back as 0.

sum({$<[ACCOUNT Code] = {ValidAccounts}>} [Amount])

ValidAccounts is a string field and looks like this 1234,2*

Any help will be aprreciated.

Using Field names as values in set analysis

Wed, 09 Dec 2009 22:36:54 GMT

Hi Martin,

Here you are a possible solution using a variable.

I hope this help you.

Best regards.

Using Field names as values in set analysis

Wed, 09 Dec 2009 22:43:26 GMT

You can set up a static Set Analysis expression using:

sum({$<[ACCOUNT Code] = {1234,"2*"}>} [Amount])

I think anything with an asterisk needs to be in quotes, but straight numbers don't need it. You should be able to put quotes around all of them, so I would do that for simplification.

Now, I'm a little confused as to what ValidAccounts is and where it comes from. Is it a field as in for every record, you have a ValidAccounts value? Does it change for each record?

If ValidAccounts only has one value across the app, you can use it in Set Analysis. If it's a variable, the syntax is:

sum({$<[ACCOUNT Code] = {$(ValidAccounts)}>} [Amount])

Keep in mind, you need quotes around each value, so make sure the value of the variable has them.

If ValidAccounts is not a variable, but a field that is the same throughout the app, you can use:

sum({$<[ACCOUNT Code] = {$(=Max(ValidAccounts))}>} [Amount])

Again, you need quotes around each value.

If ValidAccounts can be different for each record in your table, then I don't think Set Analysis will do the job. A Set Modifier in Set Analysis can't really reference a dimension or other value on a record by record basis.

Remember, when using a dollar sign expansion in Set Analysis, put your expression into a table, but don't give it a label. When the chart is rendered, the label will be your Set Analysis expression with the dollar sign expansion evaluated.

Using Field names as values in set analysis

Sun, 03 Jan 2010 23:12:33 GMT

Hi Miguel,

Is there anyway to reference a field name as a variable inside for the 1st part of the set analysis expression?

Taking in your example, what if the [ACCOUNT Code] field should be referenced as a variable as well?

Appreciate your help.

Regards

Using Field names as values in set analysis

Mon, 04 Jan 2010 15:46:38 GMT

Hi Indiana,

I really don't know what you mean with using a variable in the first part of the set analysis expresion, do you want something like sum({$<[VAR_CODE] = {"1*"}>} [Amount])?

That doesn't have logic, you can't say variable equal to ... because that doesn't return a set of data.

Best regards.

Using Field names as values in set analysis

Mon, 04 Jan 2010 23:11:52 GMT

Hi Miguel,

Probably it does not make sense but I was trying a solution as described below:

You time list boxes for Year, Quarter/Year, Month/Year and Date. Each of those list boxes represent a field in the model.

If you select something on the Year you still have additional selections you can make either in the Quarter/Year, Month/Year and Date fields but if you select something on the Month/Year field you're immediatly constrainted on the selections you can make on the Quarter/Year and Year Fields.

So, when dealing with dimensional hierarchies I'm interested to pick the lowest level field where a choice was make and not the entire set of fields.

On the expression side the relevance it's impressive because of size of the set expression. Take for example:

sum({<[Year]={$(v_mychoosenyear)}, [Quarter/Year]={{$(v_mychoosenquarter)}, [Month/Year]= {$(v_mychoosenmonth)}, [Date]={$(v_mychoosendate)} >} Sales)

My initial questions has to do with the following:

It is possible to create and store in a variable which is my lowest level field where a choise was made (if (not isnull(getfieldselections([Date])), [Date], if....). Imagine I call this variable v_MyChoosenField

Then I could substitute the set expression by:

sum({<$(v_MyChoosenField)={$(v_mychoosenvalue)} >} Sales)

Am I dreaming or it's feasible on your opinion?

Cheers

Joao

Using Field names as values in set analysis

Tue, 05 Jan 2010 10:28:22 GMT

Hi Joao,

You are a lucky guy, apparently it works as you expect, there is a file with a small example, there are three fields with their respective values, if you select any value of them the set analysis expression is evaluated based on it.

But .. what if ...?

* User make a multiple selection on a field?
* There is no selection on any field?

Well, I hope this help you.

Best regards.

Using Field names as values in set analysis

Tue, 05 Jan 2010 10:45:54 GMT

Ups .. I can't upload the file, there is a time out error, I'll try later.

Using Field names as values in set analysis

Tue, 05 Jan 2010 13:13:10 GMT

Hi Miguel,

Thank you very much.

I'll try this approach and get back to you with the results.

Cheers

Joao