Reverse display in a pie chart in obiee

Question

I have a problem in OBIEE, I create pie chart but it dosen't show correctly. I write this measure : case when "n_dim_city_senderDim"."sendercityname"='تهران' then '1' when "n_dim_city_senderDim"."PROVINCENAME" IN ('البرز', 'تهران') then '2' end

but 2 should bigger than 1. It shows inverse How can I correct?!

I threw the measure in different places but it didn't work

Answer 1

If I understood you correctly, this

case when "n_dim_city_senderDim"."sendercityname" ='تهران'           then '1' 
     when "n_dim_city_senderDim"."PROVINCENAME"  IN ('البرز', 'تهران') then '2' 
end

returns 1, while you expect it to return 2. That's because there are rows that contain both province_name as well sendercityname equal to / are contained in values you specified.

If provincename should take precedence, switch conditions to

case when "n_dim_city_senderDim"."PROVINCENAME"  IN ('البرز', 'تهران') then '1' 
     when "n_dim_city_senderDim"."sendercityname" ='تهران'           then '2' 
end

---

[EDIT] To illustrate it:

Sample data:

SQL> select * from test order by 1;

SENDERCITYNAME  PROVINCENAME
--------------- ---------------
Montreal        Quebec
Winnipeg        Manitoba

First query:

SQL> select
  2    sendercityname,
  3    provincename,
  4    case when sendercityname = 'Montreal'            then 1
  5         when provincename in ('Quebec', 'Manitoba') then 2
  6    end as result
  7  from test
  8  order by 1;

SENDERCITYNAME  PROVINCENAME        RESULT
--------------- --------------- ----------
Montreal        Quebec                   1
Winnipeg        Manitoba                 2

Second query, whose CASE expressions are opposite from the 1st query:

SQL> select
  2    sendercityname,
  3    provincename,
  4    case when provincename in ('Quebec', 'Manitoba') then 1
  5         when sendercityname = 'Montreal'            then 2
  6    end as result
  7  from test
  8  order by 1;

SENDERCITYNAME  PROVINCENAME        RESULT
--------------- --------------- ----------
Montreal        Quebec                   1
Winnipeg        Manitoba                 1

SQL>

See the difference?