Issue with expression calculating a figure for last month

[#1]

Hi

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

[tresesco]

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

[#3]

Hi

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

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

[ashfaq_haseeb]

Check if this works

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

or

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

Regards

ASHFAQ

[tresesco · Accepted solution]

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

[#6]

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.

[#7]

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