topic Text Sort in Pivot Table in QlikView

Text Sort in Pivot Table

Mon, 27 Jul 2009 23:03:39 GMT

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!

Text Sort in Pivot Table

Anonymous — Tue, 28 Jul 2009 01:12:55 GMT

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

Text Sort in Pivot Table

Tue, 28 Jul 2009 01:21:20 GMT

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??

Text Sort in Pivot Table

Anonymous — Tue, 28 Jul 2009 01:53:59 GMT

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.

Text Sort in Pivot Table

Tue, 28 Jul 2009 02:07:49 GMT

The easiest answers always elude me...

Thank you for your help Michael!