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