Can YOU Help me?? Case-When condition

building semantic layer
1

Hi to All,

I have this Dimention


CASE 
WHEN NationalityCode = '100' THEN 'XXXXXX'
ELSE 'YYYYYY'
END

I want to add the total as a part of the Case-When Condition, I do this modification but it is not working right


CASE 
WHEN NationalityCode = '100' THEN 'XXXXXX'
WHEN NationalityCode <> '100' THEN 'YYYYYY'
WHEN (NationalityCode = '100' OR WHEN NationalityCode <> '100') THEN 'Total'
END

I need to have a diminstion that give me three columns X,Y, and Total

I need this because i need to insert other diminsion below it in the report,
so any one can help me or any one have other idea? :roll_eyes:

DataAdmin :bahrain: (BOB member since 2006-08-23)

2

Hi,

I think better to not to use single quote for 100 as it is number.OR simply make use of nested IF condition

eg. If (NationalityCode = ‘100’) then ‘XXXX’ else if (NationalityCode <> ‘100’) then ‘YYYYY’ else ‘Total’

Hope it will help you…

prashil123 :india: (BOB member since 2006-08-25)

3

Hi again,

I will Explain my problem in details…

I have this data


ID          NationalityCode          Sex         Name
----------------------------------------------------------------
123                100                    M            plplplplpl
234                100                    M            okikjjkhkh
244                400                    M            jkhjkgkjghkj
755                250                    F             dfglkjkljdfgdf
822                100                    F             iouilpiouio
200                100                    F             jhgjkghjg
390                900                    M            uyjhgfjhgdf
678                100                    M            hgjfjkhkjhj

100 is Bahraini
and other code is Non-Bahraini

I have three diminsion


WorkerCount
Naionality
Sex

i build this report


----------------------------
| Bahraini    | Non-Bahraini|
----------------------------
|  M   |  F   |  M   |   F  |   
----------------------------
|  3   |  2   |   2  |   1  |
----------------------------

I want to have this report


------------------------------------
| Bahraini    | Non-Bahraini| Total |
-----------------------------------
|  M   |  F   |  M   |   F  | M | F |
-----------------------------------
|  3   |  2   |   2  |   1  | 5 | 3 |
-----------------------------------

If I use the sum in the report i will get the sum for the total only not for each Male and Female

any help? :expressionless:

DataAdmin :bahrain: (BOB member since 2006-08-23)

4

Wouldn’t you be better off creating 3 new measures?


SUM(CASE WHEN NationalityCode = 100 then 1 END)

name that ‘Bahrain’

SUM(CASE WHEN NationalityCode != 100 then 1 END)

name that ‘Other’

@select(Bahrain)+@select(other)

name that ‘Total’

dessa :madagascar: (BOB member since 2004-01-29)

5

Hi

As per your code above, it will never reach the third condition i believe, becuase when NAtionalitycode=‘100’ first condition will be true and you will get ‘XXXXXX’ when nationalitycode<>100 then you get ‘YYYYYY’ and your code exits, you will never reach your third condition of NationalityCode = ‘100’ OR WHEN NationalityCode <> ‘100’ so you will never get total as an output of this code.

siddhartha.dubey :india: (BOB member since 2006-09-13)