10 Replies Latest reply: Jul 18, 2011 6:06 PM by w00master RSS

    Variables in set Analysis

    malika ouenza

      How can I use a variable in a set analysis expression?

        • Variables in set Analysis

          I just tried this and it seems to work: Sum({$<"$(variable)"={'value'}>} Sales).
          So add double quotes around the variable.

            • Variables in set Analysis
              malika ouenza

              Thank you so much for the reply, actually i am trying to have the variable not in the identifier but in the field value, in your example it will be the Sales

                • Variables in set Analysis
                  Oleg Troyansky

                  if you want to replace the "expression" (e.g. the Sales) by a variable that holds a definition of your dynamic expression - you can do it, it has nothing to do with the Set Analysis itself - these are two separate parts of the overall aggregation.

                  For example:


                  vFormula = '(Sales-Cost)'


                  sum({Set Analysis Condition } $(vFormula) )

                  is identical to a "static expression:

                  sum({Set Analysis Condition } (Sales-Cost) )

                  I hope this is what you had in mind...

                    • Variables in set Analysis
                      malika ouenza

                      That exactly what I had in mind, but when I enter my variable, no values are returned, I get nothing just null. I tested my variable and it works if I do not combine it with the set analysis condition.

                      my example is :


                      ({1<OBJECT ={"17*"}>} $(Current_Year_Balance))


                        • Variables in set Analysis

                          So your variable $(Current_Year_Balance) contains a lot of values, but you only want to summarize the values where OBJECT = '17*' (you should use single quotes here btw. Better for performance). This means there must be a connection between the values in the variable and the values in OBJECT.

                        • Variables in set Analysis
                          Corne Vermaak

                          So I have a formula that looks like this:

                          ($(vSalesTY) - $(vReturnsTY)) / ($(vNetTransactionsTY))

                          Can I then add set analysis to the above calculation as follows, to get a result for a set timeperiod?


                          Sum( {$<FinTxtMonth = {'$(MonthSelected)'} >} (($(vSalesTY) - $(vReturnsTY)) / ($(vNetTransactionsTY))))


                          If not, how do I use set analysis on the original expression


                            • Variables in set Analysis

                              I have a similar query to this, are you able to use a variable as the value of the set analysis?


                              Count({<[Has Target] = {'Yes'},[SLX Relationship RM Name]-={[RM Selection]}>} DISTINCT [SLX Account ID])

                              In the above RM Selection is my variable would that work?


                            • Re: Variables in set Analysis

                              Oleg (and everyone)-


                              This is wonderfully helpful. That being said, 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:





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



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



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





                              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.



                        • Re: Variables in set Analysis



                          Have trouble using a variable in set analysis...


                          This works fine (hard coding a date 12 months ago), I get a count of 14:

                          count({1<InceptionDate = {">=07/06/10"}>} PolicyNo)



                          But as this needs to be a rolling count I set up a variable:



                          v12MonthsStartDate = AddMonths(today(),-12)

                          Then replaced the date with the variable:



                          count({1<InceptionDate = {">= v12MonthStartDate"}>} PolicyNo)



                          but now I get a count of 0...



                          Any ideas why this isn;t working???


                          Thanks, Mike

                            • Re: Variables in set Analysis
                              Steve Dark

                              Hi Mike,


                              A couple of things, first up Set Analysis is sensitive to date formats, so I would suggest wrapping your variable assignment in a date function:


                              date(AddMonths(today(), -12), 'DD/MM/YY')


                              Also I would recommend using dollar sign expansion in the Set Analysis, like this:


                              count({1<InceptionDate = {">=$(v12MonthStartDate)"}>} PolicyNo)


                              Personally, I would also set up the variable in the load script (provided your load script runs after midnight on the day you are looking at data) so that you are putting in a fixed rather than a calculated value:


                              let v12MonthStartDate = date(AddMonths(today(), -12), 'DD/MM/YY');


                              This way you can check the date in the variable to ensure it is appearing as it is requried in the statement.


                              Hope that all makes sense.