10 Replies Latest reply: Apr 3, 2012 3:32 AM by Maikol Miorandi RSS

what if analysis

Maikol Miorandi

I have to make a what-if analysis, where the user sets the values of some variables (using some sliders) and then qlikview calculates the results based on the variables

 

in this topic  http://community.qlik.com/message/203731#203731  they told me i have to make a partial reload to calculate the results

 

but i cannot do this since the end user cannot do a partial reload using qlikview via web

 

so is there a way to create working what-if analysis in qlikview??

 

Edited:
i have attached an example, with a simple what if analysis and alla the things discussed in this topic

  • Re: what if analysis
    swuehl

    If you can't do a (partial) reload, you would need to move your new field calculations to the frontend, i.e. use an expression or calculated dimension instead. Not sure if this is possible or easy doable in your case, to help you more, you would need to give more information about your data model, the what-if analysis you want to do and the calculations used in the script.

     

    Probably best to post a small demo app that demonstrates your issue here to the forum.

     

     

    • what if analysis
      Maikol Miorandi

      I'm not an expert, thus correct me if i'm wrong:


      i have some data loaded from an excel file, that are saved into a field

      i have a variable which the user can modify using the slider

      i have to dinamically make calculations with those 2 data (for example multiply the values taken from excel and the variable)

       

      if i understand correctly, these calculations can only be done within the script

      otherwise i would need something like X variables to contain all the values of the excel file

       

      i am really confused about that

      • what if analysis
        swuehl

        I think it should be possible to calculate all is needed in the frontend.

         

        Could you post a small sample (e.g. the excel file), doesn't need to contain your real data, but should demonstrate your issue?

        • what if analysis
          Maikol Miorandi

          I have edited the discussion and attached an example

          • Re: what if analysis
            swuehl

            If you want to calculate something in the frontend, you need to use a chart object where you can use expressions, just a table box will not do the job.

             

            For example create a straight table chart object with dimension name and as expression

             

            = quantitiy * vVariable

             

            This should dynamically re-calculate your values when you move the slider / change the variable.

             

            Hope this helps,

            Stefan

            • what if analysis
              Maikol Miorandi

              Ok, i wrote a bad example: my situation is a little more complicated

              I have changed the attached file with a new one

               

              As you can see i have 3 variables, and based on the content of the "name" field i load a different value and assign it to the "variable" field

              This way i can then use this field to make sum and other aggregations

               

              But i have to do this outside the load script

              If i make an expression in the chart table (as your example shows), i cannot use that value outside the chart

              • Re: what if analysis
                swuehl

                That's indeed only a little bit more complicated.

                 

                You just need to convert your script expression in a front end expression, so I think you can use an expression like

                 

                =pick(match(name,'a','b','c'),vVariableA,vVariableB,vVariableC)

                 

                to pick the right variable for your name and then just multiple by your quantity or if you want to have an aggregate over all your names:

                 

                =sum(aggr(quantity*pick(match(name,'a','b','c'),vVariableA,vVariableB,vVariableC),name))

                 

                See also attached,

                Stefan

                • what if analysis
                  Maikol Miorandi

                  Thanks for your time Stefan, and thanks for the hints

                  But this way it's gonna be too complicated soon

                   

                  A friend of mine suggests to define some variables to contain the values i need

                   

                  But even in this case i get into some unexpected errors

                   

                  I have edited the discussiond and attached a new file

                   

                  As you can see i create vVariable to contain tha value i need, whit the various "if" in the expression

                   

                  Then i create vResult, expecting to multiply vVraiable with quantity

                   

                  But as you can see in th table it only shows some -

                   

                  Is this an expected behavoir?

                   

                  How can i make calculations with variables?

                  • Re: what if analysis
                    swuehl

                    You are almost there.

                     

                    Your vVariable including the multiple if() functions is not much different from the pick(match()) combination, by the way.

                     

                    If you want to use variables, one big point is to know how variables are evaluated (in which context). See

                    http://www.qlikfix.com/2011/06/08/not-all-variables-are-created-equal/

                    for quite a good explanation of what I mean.

                     

                    If you want to evaluate the sum of vVariable*quantity e.g. in a text box, I believe you need advanced aggregation, because you need to evaluate the variable per dimension name.

                     

                    Please see attached.

                     

                    Hope this helps,

                    Stefan

                    • what if analysis
                      Maikol Miorandi

                      I didn't understand the mechanichs of = and $ with variables, but at least i got what i needed =)

                       

                      Anyway to get the total in the textbox i can just:
                      =sum($(vResult))

                       

                      Thanks for all your help!

                       

                      Maik