1 Reply Latest reply: Feb 12, 2014 1:38 AM by Nagaraj Rangaiah RSS

    If and set analysis not working for this case scenario

    Oscar Marquez

      Hi All,

       

           I'm pulling my hair with this one, I feel like I'm almost there.... I've got a big table with 1 year of data, I've simply added a rowno() 'RN' as one more column in my load script increasing +1 along all my table.

       

           I have to take the corresponding value for (90%)th  of records for each month. I'm already calculating it in a straight table, the problem is when I try to use the expression in set analysis or if functions.

       

           Take a look to this table... Min/max = min/max row for that month. Row = # of row that corresponds to the 90% item of max rows.

          

      The column I'm trying to get is TAT, so in last column the expression is:  =if(RN=$(=min(RN)+(round(count(RN)*0.9))-1),TAT) or set analysis: =max({<RN={$(=min(RN)+(round(count(RN)*0.9))-1)}>} TAT)

       

           The problem is, as you can see, circled in red, that it is comparing just vs the global value, not the one in each row. Any ideas how to make it work?

       

      90.png