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

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

Mon, 28 Jan 2013 11:26:24 GMT

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 — Mon, 28 Jan 2013 11:47:24 GMT

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!!

Mon, 28 Jan 2013 12:06:18 GMT

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?

Tue, 29 Jan 2013 07:15:17 GMT

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))