topic Re: Sort by calculated dimension in pivot table in QlikView

Sort by calculated dimension in pivot table

Tue, 05 Feb 2013 15:58:49 GMT

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!

Re: Sort by calculated dimension in pivot table

Tue, 05 Feb 2013 16:16:18 GMT

pl upload the sample application..

Re: Sort by calculated dimension in pivot table

Wed, 06 Feb 2013 17:35:01 GMT

I have added a qvw... thanks

Re: Sort by calculated dimension in pivot table

Wed, 06 Feb 2013 18:04:13 GMT

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

Re: Sort by calculated dimension in pivot table

pover — Wed, 06 Feb 2013 18:19:17 GMT

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

Re: Sort by calculated dimension in pivot table

Wed, 06 Feb 2013 20:09:58 GMT

Thanks this worked.