11 Replies Latest reply: May 17, 2011 7:59 PM by John Witherspoon RSS

    Set Analysis Variable

      Could someone help me with the following please ?

       

      I have an expression like this:

       

      if(AlternativeSupply='Site1',SUM({<[Measures-Groups]={'Cost'},[Versions]={'Actual'},[Sub Sites]={'Site1'}>} Total Data) )

       

      This works fine but I would like to embed the AlternativeSupply into the Sub Site to save me a long list of 'if' statements something like this:

       

      SUM({<[Measures-Groups]={'Cost'},[Versions]={'Actual'},[Sub Sites]={AlternativeSupply}>} Total Data)

       

      but I don't seem to be able to get the syntax right ?

       

        • Set Analysis Variable

          if AlternativeSupply is a value inside the Sub Sites Field, you can try to add the  ' '  that you use in all the other selections.

          • Set Analysis Variable
            Martin FAVIER

            Hi,

            I think you have to do this :

            [code]SUM({<[Measures-Groups]={'Cost'},[Versions]={'Actual'},[Sub Sites]={"$(=Only(AlternativeSupply))"}>} Total Data)[/code]

            Hope that helps you

             

            Martin Favier, Acssi, Lille / Nord / France

            • Re: Set Analysis Variable
              Nagaian Krishnamoorthy

              The syntax for field-selection expression in set analysis expression seems to be FieldName = {FieldValue}

               

              You have tried to use FieldName = {FieldName}

               

              Probably, that is why it is failing.

               

              If you add a field to your file after applying the 'If' conditions and add that field in your set expression, it will work. Probably it is a work-around.

               

              As I did not have your data or qvw file, I checked this using my own data in the attached qvw file.

               

              Hope this example helps you to solve your situation.

                • Set Analysis Variable

                  I think that the issue might be that there is no single value for Alternative supply once I have in inside the Sum/Total Function that is why the 'If' version of the expression works and the combined version doesn't. Is there a way to set a variable before the SUM Function that I can then use within it ? So something like:

                   

                  Variable1 = AlternativeSupply

                  SUM({<[Measures-Groups]={'Cost'},[Versions]={'Actual'},[Sub Sites]={Variable1}>} Total Data)

                • Re: Set Analysis Variable

                  Hello,

                  I think Martin Favier was near the solution.

                   

                  I think it's not [Sub Sites]={"$(=Only(AlternativeSupply))"} but [Sub Sites] ={"$(AlternativeSupply)"}.

                   

                  This will replace the variable AlternativeSupply by its value.

                   

                  Hope this will solve your problem.

                   

                  Quent

                    • Re: Set Analysis Variable

                      Thanks,

                       

                      This is definately along the lines of what I'm after but it still doesn't seem to work I have put both those lines in the set analysis along with an example of what I'm trying to achieve.

                        • Re: Set Analysis Variable

                          Hello,

                           

                          I don't know if it's normal, but in the example you gave, the variable AlternativeSupply doesn't exist.

                           

                          That is the reason why it doesn't work here, if in your real document, this variable exists and has a value that is supposed to work, I don't know how to solve your problem, sorry.

                           

                          edit : I tried to create the variable and put the value "Site1" in it, and it seems to work, the 4th colum (my solution) took the value "14"

                           

                          Quent

                            • Re: Set Analysis Variable

                              Sorry it should have been AlternativeSite but this isn't the issue.I am able to put a single value in a variable as you have but it falls down when I introduce a variable value in the variable.

                               

                              Thanks for your help anyway I can work around it for now it just limits what I can do in the future with the data.

                               

                              If it was excel I would use the INDIRECT function that you can use to build function text with variables but I can't find a similar function in Qlikview. Maybe it's not possible.

                               

                                • Re: Set Analysis Variable
                                  John Witherspoon

                                  You can't do it with set analysis because a set is analyzed for the entire chart, not once per row.  Well, technically you CAN do it with set analysis, but it's rather complicated, and not always faster than an if(), so let's set that aside for now.

                                   

                                  How about just changing the data model to add an AlternativeCost?

                                   

                                  LEFT JOIN (YourTable)
                                  LOAD
                                  Site as AlternativeSite
                                  ,Cost as AlternativeCost
                                  RESIDENT (YourTable)
                                  ;

                                   

                                  Of course if you have 50 different fields you want to see for the alternative site, this isn't very practical, but it seems doable for just a field or two.  Denormalization isn't a problem since this is all read only, and QlikView's compression should take care of any extra space requirements.