Sorting Issues in a Pivot Table

803253

All,

I'm trying to trick OBIEE to sort by a calculated item. I understand you can not sort by a calculated item so I have created a custom attribute and it has the below formula structure:

CAST(RANK((FILTER(measure)as CHAR

When I have less than 10 items, it works like a charm and I can sort Ascending to give me a 1 through 10 sort. When I have anything greater than 10, OBIEE sees 10 as the next in line behind 1. I understand this is because I have it casted as a CHAR, but when I remove it, I'm getting 100s across the board. I also get 100s across the board when casting it as an INT.

Issue Picture:

My formula

CAST(RANK((FILTER(ifnull("PMPM Measures"."PMPM Allowed Amount Calc-without Interest",0) USING (case   when  "Service Date"."Service Year Month" BETWEEN @{P_Hia_Svc_Yearmo_From_P1}{201401} AND   @{p_HIA_SVC_YEARMO_TO_P1}{201412}  then 'Period 1'  when "Service Date"."Service Year Month" BETWEEN @{P_Hia_Svc_Yearmo_From_P2}{201501} AND @{p_HIA_SVC_YEARMO_TO_P2}{201512} then 'Period 2'   end = 'Period 2')) - FILTER(ifnull("PMPM Measures"."PMPM Allowed Amount Calc-without Interest",0) USING (case   when  "Service Date"."Service Year Month" BETWEEN @{P_Hia_Svc_Yearmo_From_P1}{201401} AND   @{p_HIA_SVC_YEARMO_TO_P1}{201412}  then 'Period 1'  when "Service Date"."Service Year Month" BETWEEN @{P_Hia_Svc_Yearmo_From_P2}{201501} AND @{p_HIA_SVC_YEARMO_TO_P2}{201512} then 'Period 2'   end = 'Period 1'))) / FILTER(ifnull("PMPM Measures"."PMPM Allowed Amount Calc-without Interest",0) USING (case   when  "Service Date"."Service Year Month" BETWEEN @{P_Hia_Svc_Yearmo_From_P1}{201401} AND   @{p_HIA_SVC_YEARMO_TO_P1}{201412}  then 'Period 1'  when "Service Date"."Service Year Month" BETWEEN @{P_Hia_Svc_Yearmo_From_P2}{201501} AND @{p_HIA_SVC_YEARMO_TO_P2}{201512} then 'Period 2'   end = 'Period 1'))) as CHAR)

Thanks

803253

Any help would be great.

Thanks,

cesar.advincula.o

Hello,

I got something similar, please check these steps, in this link:

sorting on measure column in a pivot table

Hope this help you.

Kind Regards,

803253

Thank you - that did not fix my issues.

I do notice when I change it from CHAR to VARCHAR(9), the sorting begins to work, but when it turns negative, it isn't correct. See example

.06

.05

.01

-.94

-.45

-.03

RamaYalala

Add another column for measure and apply RANK to that column and sort it. It worked for me in the past. You can always hide the column.