Help on using mid() function with field name in SetAnalysis

[#1]

Hi, Can anyone help me to use mid() function with field name in SetAnalysis. Here is my example,

column1 Account has values like

1000,

2000,

3000,

4000...

column2 Segment has values like

1111.1111.1000.1111,

2222.2222.2000.2222,

3333.3333.3000.3333

4444.4444.4000.4444....

so the account = mid(segment,8,4)....my account resides in mid(segment,8,4) position.

I want to calculate this using SetAnalysis...I tried the following but I am getting 0 as value

=sum({$<ACCOUNT={$(=Mid(SEGMENT,8,4))}>} (salesA-salesB))

=sum({<ACCOUNT={'$(=(mid(SEGMENT,8,4))'}>} (salesA-salesB))

If i hadcode the value 1000/2000... to my expression it is working as expected. But when i pass the filed name as agument it fails

Thanks in advance.

[sujeetsingh]

Hi,

you can use other string functions too

try like this

sum(if(SubField(Segment,'.',3)=Account,Amount,0))

Try like this

Hope it help!!

[#3]

Thannk you for the reply...It works in that way. But i forget to tell one more requirement i have...

i need to bring more that one account value..

=sum({$<ACCOUNT={$(>=Mid(SEGMENT,8,4)) <=Mid(SEGMENT,8,4))}>} (salesA-salesB))

i want to use >= and =< expression so that I will get more that one account's total..

any more clue?

[#4 · Accepted solution]

thanks anyway! it worked using your method...but i used the <= and >= operators inside the same formula

sum(if(ACCOUNT>=(SubField(SEG,'.',3)) AND (ACCOUNT<=SubField(SEG,'.',4)),(salesA-salesB),0))