Might be missing function command in Logical function-Creating calculated field?

[Anonymous]

I about to create a calculated fields associated with some logical expression using "and","or" to define the calculation within specified range of accounts. When i use the syntax in the expression using 'and' function is not recognizing as predefined function where as the same expression works well with Data load editor script.

IF([ACCOUNTNUM]>=500000 and [ACCOUNTNUM]<=599999,'NET INCOME BEFORE ZAKAT','OTHERS')..

if I do in data load editor i'm facing error in Data synchronization. To avoid data synchronization, i need to use table edit and do calculated field.

Is there is any way to handle this issue.

[Anil_Babu_Samineni]

What if  you add this in Calculated dimension?

[Anonymous]

not showing the preview and showing error as attached, for your kind information on the Fx (function tab, i found different function command but not the 'and', 'or' to do the calculation. attached screenshot for your verification.

and not showing any result in preview result.

[Anil_Babu_Samineni]

I've tested that. It's working as expected. Will you able to provide sample to test

[Anonymous]

I don't find a way to attach excel file. but this is the sample and range i need to calculate same as description as range (manual)

[Anil_Babu_Samineni]

Please check this Uploading a Sample

[Anonymous]

i attached excel file test1 on the first thread

[Anil_Babu_Samineni]

What i understand here is you are not support to use operations or functions which are not cover from section provided by Sense.

So, Instead create manual using If..Else condition in script manually. Or else, My suggestion is this?

[Anonymous]

Some extend its work good, but we can't put a conditional expression like filter in particular range.

for example for expenses account 60000 series we have department dimension, so we need to range the expression as bucket 60000 to 699999 which hold the value for department dimension which is filed dimension1 (dept)='10' for sales and 20 for Administration.

similar as below.

IF([ACCOUNTNUM]>=600000 and [ACCOUNTNUM]<=660200 AND [ACCOUNTNUM]<>623300 and [DIMENSION]>=06 and [DIMENSION]<=06,'SALES & DISTRIBUTION EXPENSE',

but this straight forward in load script. i feel that this type of filter is missing in bucket or may i'm not aware of this function how to use.

Sorry to bother you.

[Anil_Babu_Samineni · Accepted solution]

There is no suck functionality Bucket with two conditions not available while loading. So, you should use manually within script itself using?

IF(([ACCOUNTNUM]>=600000 and [ACCOUNTNUM]<=660200) AND ([ACCOUNTNUM]<>623300) and ([DIMENSION]>=06 and [DIMENSION]<=06),'SALES & DISTRIBUTION EXPENSE',

Note - What is the usage of Blue part here? You just call DIMENSION = 06 instead >= and <= with same value? Does it make sense?