Problem with expression

[#1]

Hi

My expression -

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month={$(vMaxMonth)},Drawer={'7'}>}JrnAmt)

is giving me the correct sum for journals for Nov 2011. However when I change the expression to -

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month={$(vMaxMonth-1)},Drawer={'7'}>}JrnAmt)

which I hoped would give me the value for Oct 2011, it doesn't work.

I also tried this -

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month={$((vMaxMonth)-1)},Drawer={'4'}>}JrnAmt)

How can I fix this?

Regards

Danie

[Michiel_QV_Fan]

Untested:

Have you also tried:

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month={$(vMaxMonth)-1},Drawer={'4'}>}JrnAmt) ?

[jagan]

Hi,

Please check

What does vMaxMonth variable holds, it holds Month name or number?

What values the Month column hlods, it holds Month names or numbers?

Check whether Month column and vMaxMonth holds month numbers.

Attach a sample, it would be easier to find and fix the issue.

Regards,

Jagan.

[#4]

Hi

Yes I tried that one

Regards

[#5]

Hi Jagan

I'm adding the model for you to peruse.

If it asks for username and pw it is 'xbi' and 'xbi01'

Thank you

Danie

[jagan]

Hi,

Add one more column in your script to hold Month number

Month(RefDate) * 1 as MonthNum - this will return month number instead of month name

Variable should be vMaxMonth =Max(MonthKey)

and now expression should be

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},MonthKey={$(vMaxMonth-1)},Drawer={'7'}>}JrnAmt)

and

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},MonthKey ={$(vMaxMonth)},Drawer={'7'}>}JrnAmt)

Regards,

Jagan.

[#7]

Hi Jagan

I'm a bit confused - We now have 'MonthNum' but where does 'MonthKey' come from?

Regards

[jagan]

Sorry!  Use MonthNum in place of MonthKey. 

Regards,

Jagan.

[#9]

Hi ,

Simply you could do one thing in the front end only.

Make variable name as VMaxMonthtest= month(num(vMaxMonth)-1)

Use this variable in your expression

Hope this will help you

Regards,

Nitin Jain

[jagan]

Hi Nitin,

I checked your expression

=num(vMaxMonth)-1 returns 10

and when you pass 10 to Month(), it gives Jan, because the date(10 in number format) will fall in Jan.

Regards,

Jagan.