1 Reply Latest reply: Dec 3, 2012 2:22 PM by Stefan Wühl RSS

    Problem with calculating fractiles by dimension

    Lyndon Nelson

      Hi,

       

      Having a problem calculating a fractile broken out by dimension when there are multiple records in the data set. Basically, if I select a single part that has several associated records, I get the correct fractile output. If I select more than a single part, all of the fractile output is all messed up, usually giving a single value for all fractile values. This is the expression I am trying to put into the pivot table. Unfortunately, I can't submit the application or a data set, but if you can take a quick look at the expression and tell me if I am doing something wrong, I would appreciate it....

       

       

      =If(

                Min(

                          If(

                                    TRR_DAYS >= $(=Ceil(Fractile ({<STATION={"OFF-STATION"},ONE_YEAR={"-1"}>}TRR_DAYS, 0.9))),

                                    TRR_DAYS,

                                    Null()

                                    )

                          )

                < 100,

       

                Min(

                          If(

                                    TRR_DAYS >= $(=Ceil(Fractile ({<STATION={"OFF-STATION"},ONE_YEAR={"-1"}>}TRR_DAYS, 0.9))),

                                    TRR_DAYS,

                                    Null()

                                    )

                          ),

       

                99

      )

        • Re: Problem with calculating fractiles by dimension
          Stefan Wühl

          Due to the dollar sign expansion, the fractile function is only evaluated once, not per dimension value.

           

          I assume you are using the dollar sign expansion to get around the aggregation-function-within-aggregation-function-not-allowed, but have you considered advanced aggregation (aggr() function?

           

          Regards,

          Stefan

           

          P.S. You don't need to post any confidential or even your real data here, but a small sample app with mock up data and chart objects / expressions that demonstrate your issue will almost always help us tremendously to help you.