topic Re: Expression error in QlikView

Expression error

Wed, 22 May 2013 06:28:45 GMT

AOA

I wand to to show the change in stock quantity..

whihc is currnet month stock minus previous month stock

where STOCK_NAME is IT STOCK

and STOCK_ID not equal to 0

NOTE: in my case current month is APR and previous month is FEB because data loaded with the gap of one month.

i wrote the expression like

(

sum({<STOCK='{'IT DIVISION'},MONTH={'APR'},STOCK_ID-={0}>}BAL_QTY) -

sum({<STOCK='{'IT DIVISION'},MONTH={'FEB'},STOCK_ID-={0}>}BAL_QTY)

)

answer is 970

WHICH IS WORKING FINE but its a hardcorded value for month

i have a variable VMAXMONTH having a value of max month which is APR

So i want to put this variable in above expresion

i tried but giving answer is -8840

whats going wrong is any thing wrong with expresion?????

(

sum({<STOCK='{'IT DIVISION'},MONTH=VMAXMOMTH,STOCK_ID-={0}>}BAL_QTY) -

sum({<STOCK='{'IT DIVISION'},MONTH={'FEB'},STOCK_ID-={0}>}BAL_QTY)

)

Re: Expression error

Wed, 22 May 2013 06:31:40 GMT

kinldy read VMAXMOMTH as

VMAXMONTH, in last expression,

Re: Expression error

jjordaan — Wed, 22 May 2013 06:35:07 GMT

Hi Shariqqlikview,

try this

(

sum({<STOCK='{'IT DIVISION'},MONTH={$(VMAXMOMTH)},STOCK_ID-={0}>}BAL_QTY) -

sum({<STOCK='{'IT DIVISION'},MONTH={'FEB'},STOCK_ID-={0}>}BAL_QTY)

)

Re: Expression error

Sokkorn — Wed, 22 May 2013 06:49:39 GMT

Hi,

Here what I found the error

1. Wrong set analysis syntax

sum({<STOCK='{'IT DIVISION'},MONTH= VMAXMOMTH,STOCK_ID-={0}>}BAL_QTY)

It should be

sum({<STOCK='{'IT DIVISION'},MONTH= {$(=VMAXMOMTH)},STOCK_ID-={0}>}BAL_QTY)

2. In this case VMAXMOMTH return in number format. Ex. you have max month is Apr then VMAXMOMTH=4 So your set analysis will calculate base on this

sum({<STOCK='{'IT DIVISION'},MONTH={4},STOCK_ID-={0}>}BAL_QTY) ==> That give you wrong target.

Solution

1. Change on variable

VMAXMOMTH = Pick(Max(MONTH), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')

2. Use below set analysis

sum({<STOCK='{'IT DIVISION'},MONTH= {$(=VMAXMOMTH)},STOCK_ID-={0}>}BAL_QTY)

Hope this help

Regards,

Sokkorn

Re: Expression error

Wed, 22 May 2013 09:14:44 GMT

Thnks Jeroen

and speciall thnks to MR Sokkorn

for such detail explaination and quick response

you guys rock now its working

thnks once again