Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
pl upload the sample application..
I have added a qvw... thanks
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
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
Thanks this worked.