Sort by calculated dimension in pivot table

[#1]

I am trying to sort my piovt table by a calculated dimension that has a label.  Can this be done?  Below is my calculated dimension...

if(fsbp.FaceAmount <= '14999' , '<15k',

if(fsbp.FaceAmount = '15000', '15k',

if(fsbp.FaceAmount > '15000' and fsbp.FaceAmount < '20000', '>15.01K<20k',

if(fsbp.FaceAmount = '20000', '20k',

if(fsbp.FaceAmount > '20000' and fsbp.FaceAmount < '30000', '>20.01K<30k',

if(fsbp.FaceAmount = '30000', '30k',

if(fsbp.FaceAmount > '30000', '>30k')))))))

The label name is WholeLifeCoverage.  I want the sort to be in the same order as my expression above.

<15K

15k

<15.01K<20K

20K

>20.01K<30K

30K

>30K

Thanks!

[#2]

pl upload the sample application..

[#3]

I have added a qvw... thanks

[#4]

Pl try following in ETL

TEMP:
LOAD Week,
     fsbp.PlanDescription,
     SaleID,
     fsbp.BasePlanType,
     fsbp.FaceAmount
FROM

(ooxml, embedded labels);

Load *,
if(fsbp.FaceAmount <= '14999' , '<15k',
if(fsbp.FaceAmount = '15000', '15k',
  if(fsbp.FaceAmount > '15000' and fsbp.FaceAmount < '20000', '>15.01K<20k',
   if(fsbp.FaceAmount = '20000', '20k',
    if(fsbp.FaceAmount > '20000' and fsbp.FaceAmount < '30000', '>20.01K<30k',
     if(fsbp.FaceAmount = '30000', '30k',
      if(fsbp.FaceAmount > '30000', '>30k'))))))) as Status
Resident TEMP

Use Staus as Dimension and in Properties->Sort->Load Order

[pover · Accepted solution]

Using "Load Order" may not work unless you order the previous table first.  If the first FaceAmount is 20,000 then '20.01K<20k' would be the first value loaded.

Try using the dual function and ordering the dimension as a number.  You can do this in the calculated dimension, but I would recommend it also be done in the script

if(fsbp.FaceAmount <= '14999' , dual('<15k',1),

if(fsbp.FaceAmount = '15000', dual('15k',2),

  if(fsbp.FaceAmount > '15000' and fsbp.FaceAmount < '20000', dual('>15.01K<20k',3),

   if(fsbp.FaceAmount = '20000', dual('20k',4),

    if(fsbp.FaceAmount > '20000' and fsbp.FaceAmount < '30000', dual('>20.01K<30k',5),

     if(fsbp.FaceAmount = '30000', dual('30k',6),

      if(fsbp.FaceAmount > '30000', dual('>30k',7)))))))) as Status

Karl

[#6]

Thanks this worked.