if condition in dimension of pivot chart

[Anonymous]

This is in pivot chart edit Dimension:

As there are many categories, i need to only few of them categories and remaining or nulls as unknown category:

if([DISEASE_SUBCAT]='Type I'or'Type II' or 'Type III' or 'Hurler' or 'Hurler-Scheie'or 'Scheie' or 'Infantile-onset'or'Late-onset' or 'Late-onset NO Cardiac Hypertrophy',DISEASE_SUBCAT,'Unknown') 

this is not working  for somehow seems to be no error in writing expression... cant find what wrong with it..

but  when I write nested if it is working

if(DISEASE_SUBCAT='Type I','Type I',

if(DISEASE_SUBCAT='Type II','Type II',

if(DISEASE_SUBCAT='Type III','Type III',

if(DISEASE_SUBCAT='Hurler','Hurler',

if(DISEASE_SUBCAT='Hurler-Scheie','Hurler-Scheie',

if(DISEASE_SUBCAT='Scheie','Scheie',

if(DISEASE_SUBCAT='Infantile-onset','Infantile-onset',

if(DISEASE_SUBCAT='Late-onset','Late-onset',

if(DISEASE_SUBCAT='Late-onset NO Cardiac Hypertrophy','Late-onset NO Cardiac Hypertrophy','Unknown')))))))))

now i need to to add extra condition for above with disease and disease subcategory showing only blank space for disease subcat which has null() for particular disease with out showing unknown

if((DISEASE_SUBCAT=isnull() and disease='Fabry'), [Disease_Subcat]=null()) as blank space

Can someone help me with this!!

Thanks

[Gysbert_Wassenaar]

Maybe this:

If(disease='Fabry' and len(trim(DISEASE_SUBCAT))=0, '', if(match(DISEASE_SUBCAT ,'Type I','Type II','Type III', 'Hurler','Hurler-Scheie','Scheie','Infantile-onset','Late-onset','Late-onset NO Cardiac Hypertrophy'), DISEASE_SUBCAT,'Unknown'))

You should probably create a field for this in the script. Your pivot table will perform better if the calculations are done on load instead of in the UI.

[Anonymous]

Thanks Wassenaar,

Everything looks fine but It did not work with null category in fabry- It is still showing as unknown for fabry,fabry disease has only blank columns in disease subcategory.

yes, to improve performance,I shall make it in script..

[Gysbert_Wassenaar]

fabry is not the same as Fabry. Make sure you use exact case-sensitive values.

[Anonymous]

Thanks alot.. Wassennar

but it is giving category of - in all other diseases too, where these should fall in unknown category.. if i suppress to show null values, it will fade out fabry.. how can i avoid - in other categories?

[Anonymous]

If(disease='Fabry' and len(trim(DISEASE_SUBCAT))=0, '', if(match(DISEASE_SUBCAT ,'Type I','Type II','Type III', 'Hurler','Hurler-Scheie','Scheie','Infantile-onset','Late-onset','Late-onset NO Cardiac Hypertrophy'), DISEASE_SUBCAT,'Unknown'))

I am able to bring  null category in fabry by above  but  for remaining it should fall in to Unknown category. but it is showing both unknown and null category.. how can i do it?

[tresesco]

Though, not sure if I understood right, try:

if(match(DISEASE_SUBCAT ,'Type I','Type II','Type III', 'Hurler','Hurler-Scheie','Scheie','Infantile-onset','Late-onset','Late-onset NO Cardiac Hypertrophy'), DISEASE_SUBCAT,'Unknown')

Or,

if you simply want to remove the null ('') part in your existing expression try like:

If(disease='Fabry' and len(trim(DISEASE_SUBCAT))=0, 'Unknown', if(match(DISEASE_SUBCAT ,'Type I','Type II','Type III', 'Hurler','Hurler-Scheie','Scheie','Infantile-onset','Late-onset','Late-onset NO Cardiac Hypertrophy'), DISEASE_SUBCAT,'Unknown'))

[Anonymous]

Hi,

I need only null category in disease: fabry because it dont have any category in it and for all other

disease,  disease subcategory as  'Type I','Type II','Type III', 'Hurler','Hurler-Scheie','Scheie','Infantile-onset','Late-onset','Late-onset NO Cardiac Hypertrophy'and/or  unknown  but when i use above i see nulls in other diseases as  null category , but it should fall in unknown category...

[tresesco]

Could you post a sample of your app ?