topic Re: Issue with expression calculating a figure for last month in QlikView

Issue with expression calculating a figure for last month

Wed, 01 Oct 2014 08:33:08 GMT

I have a report that shows me the number of orders and lines picked by week, last week, and month but not last month.

I had this report created by a developer for us as part of my training. I am still at a very basic level so cannot understand why my modification isn't working.

I created the variable vLastMonth as =Month(Now())-1

I then have a text box that has the following in it to display the number of picks for this month.

=num(count( {1 ,<Cal_Year= {$(vCurrentYear)}, Cal_Month = {$(vCurrentMonth)}> } Picks),'#,###')

I copied the text box as an object and pasted as new object and changed the variable vCurrentMonth to the one I had created vLastMonth.

So the code in my text box reads

=num(count( {1 ,<Cal_Year= {$(vCurrentYear)}, Cal_Month = {$(vLastMonth)}> } Picks),'#,###')

However rather than returning the number of picks for last month it is giving me a zero

Any help would be greatly appreciated as I don't know where to start to break this down as to why it's not working.

I am not getting any script errors when I modify it, I get expression ok.

Thanks Steve

Re: Issue with expression calculating a figure for last month

tresB — Wed, 01 Oct 2014 08:35:46 GMT

Try removing the comma, after '1': =num(count( {1 ,

Re: Issue with expression calculating a figure for last month

Wed, 01 Oct 2014 09:59:56 GMT

checked the code and there's no , after the 1

my apologies I must have caught the , as I was typing the <

Re: Issue with expression calculating a figure for last month

ashfaq_haseeb — Wed, 01 Oct 2014 10:04:39 GMT

Check if this works

=num(count( {1 <Cal_Year= {'$(=vCurrentYear)'}, Cal_Month = {'$(=vCurrentMonth)'}> } Picks),'#,###')

=num(count( {1 <Cal_Year= {"$(=vCurrentYear)"}, Cal_Month = {"$(=vCurrentMonth)"}> } Picks),'#,###')

Regards

ASHFAQ

Re: Issue with expression calculating a figure for last month

tresB — Wed, 01 Oct 2014 10:05:29 GMT

Then it would be format issue. How does your data in Cal_Month look like? 'Jan', 'Feb'....or numeric-1,2,....If non numeric try to redefine your variable like:

vLastMonth as =Month(AddMonths(Today(),-1))

And then add single quotes in set like:

Cal_Month = {'$(vLastMonth)'}

Re: Issue with expression calculating a figure for last month

Wed, 01 Oct 2014 10:10:04 GMT

Hi Ashfaq

The expression for current month works so it doesn't need ' or " adding, its when I change it to vLastMonth that I have the issues.

Re: Issue with expression calculating a figure for last month

Wed, 01 Oct 2014 10:15:57 GMT

Thanks Tresesco

Months are displayed as Jan, Feb etc. Changed my variable to match and it returned the information I expected. I didn't need the single quotes in the set analysis though.

Many thanks.

Steve