Problem with calculating fractiles by dimension

[#1]

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

)

[swuehl · Accepted solution]

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.