8 Replies Latest reply: Sep 25, 2012 4:20 PM by John Chatlani RSS

    use of possible function in set analysis expression

    John Chatlani

      Hello,

       

      I am trying to use set analysis as an expression of a pivot table chart.  There are 2 tables that are relevant to achieving the logic that is desired.

       

      One table has "projections" and the other "results".  In essence I am attempting to line up results with projections.  I have stripped down the logic a little so that I can hopefully convey the issue that I am facing.

       

      If I use an expression like this to reveal the projections version number of interest, it works.  Let's say this shows that version 1.2 is the answer.

        =max( {<projections.os=p(os)>} if(projections.pl_ver_num <= pl_ver_num, projections.pl_ver_num))

       

      The result of this expression needs to feed into another expression to finally show me the entire list of projections that are part of the 1.2 version.

       

       

       

       

      Instead, I would like to achieve this action in a single expression by extending the logic in my set analysis expression BUT it is not working as expected?  I am wondering if it is a syntax error?

       

      max( {<projections.os=p(os), projections.pl_ver={"<=$(=p(pl_ver))"}>} projections.pl_ver)

      If I replace the test for the version number in the set analysis expression above (i.e., the stuff between the {})  with {"<=1.2"} then this expression works which leads me to believe that my syntax is at fault?

      If I create a separate text box for testing, and use the possible funciton on pl_ver I can see that it returns 1.2, which is expected.

      Any help would be appreciated.

        • Re: use of possible function in set analysis expression
          whiteline _

          Hi.

          You can use 'search' camabilities of set analysis instead:

          =max( {<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num<=pl_ver_num"}>} projections.pl_ver)

           

          It should select from projections.pl_ver only thouse values that have (projections.pl_ver_num<=pl_ver_num)=true.

            • Re: use of possible function in set analysis expression
              John Chatlani

              Thank you it worked, appreciate your feedback.  Can you please help me extend this further such that I can embed the "max" inside the set analysis expression.  Again, I struggle with the syntax.

               

              I would like a single version as the outcome.  Currently the set returns a set of values 0, 1.1, 1.2.  I want 1.2 as a  result.

               

              So, something like

              projections.pl_ver={"=$(=max(projections.pl_ver_num<=pl_ver_num))"}>} projections.some_field

               

              Thanks in advance

                • Re: use of possible function in set analysis expression
                  whiteline _

                  Sorry, didn't understand your issue.

                   

                  There is max already:

                  =max( {<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num<=pl_ver_num"}>} projections.pl_ver_num)

                   

                  Of course you can sort it and return the last value with firstsortedvalue():

                  =firstsortedvalue({<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num<=pl_ver_num"}>} projections.pl_ver, -projections.pl_ver_num)

                    • Re: use of possible function in set analysis expression
                      John Chatlani

                      Apologies.

                       

                      Currently, the max() evaluates to a version # (1.2).  This is then fed into another expression to index a correspoding value (a battery current measurement in my application).  So version 1.1 may return a current value of 5mA, 1.2 a value of 8mA. 

                       

                      The max() expression that you have helped to solve returns ver 1.2, now I need to parse out the corresponding current value of 8mA.

                       

                      I am essentially trying to retrieve current[1.2] vs. current[1.1] vs current[1.0].  The max expression in the set analysis should return the 1.2, so hopefully this clarifies what I am trying to achieve:

                      projections.pl_ver={"=$(=max(projections.pl_ver_num<=pl_ver_num))"}>} projections.battery_current

                        • Re: use of possible function in set analysis expression
                          whiteline _

                          You could try this one:

                          =firstsortedvalue({<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num<=pl_ver_num"}>} projections.battery_current, -projections.pl_ver_num)

                           

                          Or this one:

                          =Only({<projections.os=p(os), projections.pl_ver={"=projections.pl_ver_num=rangemin(max(projections.pl_ver_num), pl_ver_num)"}>} projections.battery_current)

                            • Re: use of possible function in set analysis expression
                              John Chatlani

                              Both are great suggestions.  I am attempting to execute this expression in a pivot table chart.  Does this matter?  None of the ideas seem to be working.  Your logic seems sound to me.

                                • Re: use of possible function in set analysis expression
                                  whiteline _

                                  What do you use as a chart dimension ?

                                    • Re: use of possible function in set analysis expression
                                      John Chatlani

                                      I have many dimensions none of which directly feed into the expression. 

                                      Through table association,  though, there are many indirect values that are used. 

                                      For example, I have a software ID as a dimension and it is linked to an OS but OS is not directly referenced nor selected by a user.  This  is why I used the possible function to select the OS. 

                                      The same is true for the pl_ver_num field above.  The table dimension software ID also indirectly chooses the various pl_ver_num's (1.1, 1.2).  I also have a test ID that is a dimension.  In essence I store battery current for a number of IDs across various software versions.

                                       

                                      I took the first expression that you sent me (your first post) and created a field in my pivot table (for debugging) and it yields the correct version # across all dimensions.  I have created a new expression in the table, with the new suggestions that don't seem to kick in.

                                       

                                      I will add that I just tried to place your "firstsortedvalue" suggestion in a text box, becasue it makes too much sense to me, and this is working.  I am playing further to see why it is not working in the context of the pivot table.

                                       

                                      Regards