topic Re: Expression is not working in QlikView

Expression is not working

Mon, 22 Dec 2014 06:08:04 GMT

Hi,

Pls find the attached xls file along with dashboard, For Apple Product, I wanted to see Sales data from current month to future.

Not sure whats wrong in my expression: =IF([Product Cat]='Apple' and month(Date)>=Month(Today()), SUM(Sales),0)

I am trying to display data for Apple from Dec to future months

For Samsung whatever data is there.

I am using personal edition. Attached screenshot might help what I am expecting.

Appreciate your help.

Thanks,

Re: Expression is not working

lironbaram — Mon, 22 Dec 2014 06:24:17 GMT

use this expression

=sum(IF([Product Cat]='Apple' ,if( month(Date)>=Month(Today()), Sales,0),Sales))

Re: Expression is not working

amit_saini — Mon, 22 Dec 2014 06:24:38 GMT

Madu,

Like this????

Thanks,
AS

Re: Expression is not working

rajeshvaswani77 — Mon, 22 Dec 2014 06:28:39 GMT

Hi Madu,

This is the correct formula

=IF([Product Cat]='Apple' and (Num(Month(Date))>=Num(Month(Today()))), SUM(Sales),0)

Looks like you have no data that will satisfy this condition.

thanks,

Rajesh Vaswani

Re: Expression is not working

Mon, 22 Dec 2014 06:40:40 GMT

Hi Liron,

I tried this formula, but I get only Dec in 2014 for Apple product, but missing all future data for 2015 which is not supposed to be.

Kindly have a look at the attached screenshot.

Thanks,

Re: Expression is not working

Mon, 22 Dec 2014 06:42:23 GMT

No Rajesh, I have data which is satisfying and you can see the attached xls file.

Thanks,

Re: Expression is not working

jagan — Mon, 22 Dec 2014 06:44:37 GMT

Hi,

Use this expression

=Sum({<[Product Cat]= {'Apple'}, Date={'>=$(=MonthStart(Today()))'} >}Sales)

Regards,

jagan.

Re: Expression is not working

Mon, 22 Dec 2014 06:58:02 GMT

Hi Jagan,

I tried this formula

=IF([Product Cat]='Apple',Sum({<[Product Cat]= {'Apple'}, Date={'>=$(=MonthStart(Today()))'} >}Sales),SUM(Sales))

because I don't want miss Samsung product.

Formula looks like doing it by date, I believe it supposed to be on monthly data.

But I am not supposed to get Nov data for Apple product. Attached screen shot is the output of using above formula.

Thanks,

Re: Expression is not working

Mon, 22 Dec 2014 07:05:27 GMT

in your screen shot you are getting nov data for product A for year 2104 not for 2014,first correct your date then try the expression suggested by jagan

Re: Expression is not working

jagan — Mon, 22 Dec 2014 07:09:39 GMT

Hi,

That Nov is of year 2104, it is not for the year of 2014. The expression works correct even if you month or date.

Hope this helps you.

Regards,

Jagan.

Re: Expression is not working

Mon, 22 Dec 2014 07:11:39 GMT

No Gupta,

requirement is to calculate monthly basis, If I run dashboard this month, I supposed to get from Dec onwards as it is a kind of forecasting.

For Apple, Nov month data is 264 and year is 2014. for Dec it is 640 and follows...

For Samsung, its a simple calculation.. it has to get whatever data is there.

Pls let me know if I am not clear.

Thanks,

Re: Expression is not working

Mon, 22 Dec 2014 07:13:55 GMT

Oh, there was typo error, looks like correct and will get back to you if I need further help.

Thanks for the correction.

Thanks,

Re: Expression is not working

Mon, 22 Dec 2014 07:32:42 GMT

hey madu,

see you are having three years 2014,2015 and 2104 i think in your table 2104 is miss spell,because data for 2014 nov+2104 nov for product apple is 264 that you are mentioned... see in the image below

i have replaced 2104 to 2014 then the table will look like below image

now clear your requirement,if you have any doubts

Re: Expression is not working

lironbaram — Mon, 22 Dec 2014 07:35:10 GMT

ths is the correct expression

=sum(IF([Product Cat]='Apple' ,if( Date>=Today(), Sales,0),Sales))