12 Replies Latest reply: Jul 19, 2011 5:46 PM by Stefan Wühl RSS

    Set Analysis and Variables?

      All-

       

      How do I set up set analysis (with variables) to sum up specific values contained in the output of the variables?  Here's an example expression:

       

      =sum({<Product.ProdName={'$(vProd1)'}>}

       

       

      Product.ProdValue) + sum({<Product.ProdName={'$(vProd2)'}>}

       

       

      Product.ProdValue) + sum({<Product.ProdName={'$(vProd3)'}>}

       

       

      Product.ProdValue) + sum({<Product.ProdName={'$(vProd4)'}>}

       

       

      Product.ProdValue)

       

      The above expression works absolutely fine.  I need a totals column. That being said, the values contained in vProd1, vProd2, (so on...) contain either a "ProdA" or "ProdB" contained in the ProductName (which is pulled in based on the variable).  I need to sum up all values containing "ProdA" and sum up all values containing "ProdB" each in it's own column.  What's the best way of accomplishing this?

       

      Also...  I have to imagine this can be highly intensive (or could be).  I've provided a highly shorted version of this expression above.  Additionally, there are always hundreds of Products being added everyday.  So, I don't want to run into an "Out of memory" situation.  So also looking for efficient ways of handling this situation as well.

       

      Thanks!

        • Set Analysis and Variables?
          Stefan Wühl

          Hi,

           

          you can use set expression also with search strings, like

           

          sum({<Product.ProdName={"*ProdA*"}>} Product.ProdValue)

           

          which should limit the ProdNames to those containing ProdA.

           

          Alternatively, you might want to have a look at the wildmatch() function, you can call functions from set expression using dollar sign expansion.

           

          Stefan

            • Set Analysis and Variables?
              Stefan Wühl

              me again.

               

              I am just curious:

              Do you use this kind of expressions to compare a user selection (of 4 products) to total numbers?

               

              If you tell us a bit more about your business requirement, I think someone here in the forum might be able to answer your performance question.

               

              Stefan

              • Set Analysis and Variables?

                Hey swuehl-

                 

                Thanks so much for your help, that being said:

                 

                =sum({<Product.ProdName={'$(vProd1)'}>} Product.ProdValue)

                 

                vProd1 is actually a variable.  I have an input selector where the user defines the value of the field.  So, using your example that you presented... how would one adjust the expression?

                 

                Thanks!

                  • Set Analysis and Variables?
                    Stefan Wühl

                    Well,

                     

                    it should look like

                    =sum( {<Product.ProdName = {"*$(vProd1)*"} >} Product.ProdValue)

                     

                    if I understand your question correctly (I was aware of vProd1 being a variabel, I assumed that you use a literal for the total match like 'ProdA'.

                     

                    My Variable vProd1 is defined as

                    ='ProdA'

                    for example

                     

                    Stefan

                      • Set Analysis and Variables?

                        Sorry, I'm not giving enough info.

                         

                        the vProd1 variable actually is a drop down selector.  The Product Names (which appears in the drop down selector) are already defined in a table.  Within the Product Name itself contains either "ProdA" or "ProdB."

                         

                        So essentially, it goes like this:

                         

                        1. User has a drop down input selector.  He/she chooses a Product: ShoesProdA.

                        2. ShoesProdA appears in the straight table with a value of 4. Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4.

                         

                        The user has up to 30 products that they can select (in my example, I only went up to 4 hence: vProd1, vProd2....)

                         

                        Hope that clears things up.

                          • Set Analysis and Variables?
                            Stefan Wühl

                            With all the variables around, I think I missed the bottom line

                             

                            We started with a need for an expression for total column, summing up all ProdValues for ProdNames containing a certain search string, right?

                             

                            I think that you could do that with either using a literal or variable.

                            Do we still have a problem at this place?

                             

                            I don't understand "Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4."

                            Does that mean you rename the variable to its value (Variablename vProd1 gets ShoesProdA and new value is numerical value of 4)? If so, what's that for?

                             

                            If you still got problems and in case it is feasible for you, please post a small sample file, I am really curious now...

                             

                            Regards,

                            Stefan

                              • Set Analysis and Variables?

                                "We started with a need for an expression for total column"

                                 

                                I've already done this with the expression I originally posted.

                                 

                                "summing up all ProdValues for ProdNames containing a certain search string, right?"

                                Yes... this is *exactly* what I'm looking for.

                                 

                                Within the Product Names, it contains either "ProdA" *or* "ProdB"

                                 

                                 

                                "I think that you could do that with either using a literal or variable.

                                Do we still have a problem at this place?"

                                 

                                To define the Products, I'm using a variable called vProd1, vProd2, vProd3, vProd4.

                                 

                                These variables reference an input selector, when you click on the drop down (let's say for vProd1), a large dropdown list of Products appear. The user will select one of those products in the dropdown. Within the *name* of the Product in the dropdown that the user selects will contain either "ProdA" or "ProdB."

                                 

                                "I don't understand "Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4."

                                Does that mean you rename the variable to its value (Variablename vProd1 gets ShoesProdA and new value is numerical value of 4)? If so, what's that for?"

                                 

                                No, we don't rename anything, the user selects a Product from a dropdown list which is what defines the variable vProd1, vProd2, vProd3, or vProd4.

                                • Re: Set Analysis and Variables?

                                  Here's my sample QVW (and it's data) to help where I'm comin' from

                                   

                                  I'd like to create a "ProdA" Total column and a "ProdB" Total column.