Text Sort in Pivot Table

[#1]

Hello All,

I am using a formula to add a term to the end of a policy year to split into terms if a policy year has more than one term through this formula:

aggr(If(Count(Total <PolicyYR> DISTINCT Term) > 1, PolicyYR & ' ' & Term, PolicyYR), PolicyYR, Term)

The resulting set looks like this:

• 2001
• 2002 A
• 2002 B
• 2003
• 2004
• 2005
• 2006
• 2007
• 2008

The expressions used in conjunction with this work perfectly, but I can't get them to sort. When I text sort, they end up like this:

• 2001
• 2003
• 2004
• 2005
• 2006
• 2007
• 2008
• 2002 A
• 2002 B

And when I numeric sort, I get this:

• 2002 B
• 2002 A
• 2001
• 2003
• 2004
• 2005
• 2006
• 2007
• 2008

It seems like maybe I am getting some mixed formatting in the resulting set that is causing these strange sorting issues, but I don't know how to fix it.

Any suggestions would be much appreciated!

[Anonymous]

Aaron,
It works for me if sort by expression PolicyYr and by text.

[#3]

Here is what I get when I use that sorting. The property sheet is over the top of the results with those settings applied.

Isnt expression sorting unavailable when working with an expression based dimension??

[Anonymous]

I believe you cannot sort by expression if it's a calculated dimension. But you can create the actual dimension in the script using the same rule.

[#5]

The easiest answers always elude me...

Thank you for your help Michael!