use of possible function in set analysis expression

[Anonymous]

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.

[whiteline · Accepted solution]

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.

[Anonymous]

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

[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)

[Anonymous]

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

[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)

[Anonymous]

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.

[whiteline]

What do you use as a chart dimension ?

[Anonymous]

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