8 Replies Latest reply: Jan 5, 2010 8:13 AM by Joao Morais RSS

    Using Field names as values in set analysis

    Martin van Niekerk



      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

          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

              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.


                • Using Field names as values in set analysis

                  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

                      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?



                • Using Field names as values in set analysis
                  Neil Miller

                  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.