topic Problem with expression in QlikView

Problem with expression

Tue, 06 Dec 2011 08:19:35 GMT

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

Problem with expression

Michiel_QV_Fan — Tue, 06 Dec 2011 08:53:16 GMT

Untested:

Have you also tried:

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

Problem with expression

jagan — Tue, 06 Dec 2011 08:57:40 GMT

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.

Problem with expression

Tue, 06 Dec 2011 09:30:31 GMT

Yes I tried that one

Regards

Re: Problem with expression

Tue, 06 Dec 2011 09:57:32 GMT

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

Re: Problem with expression

jagan — Tue, 06 Dec 2011 10:34:12 GMT

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.

Re: Problem with expression

Tue, 06 Dec 2011 11:04:56 GMT

Hi Jagan

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

Regards

Problem with expression

jagan — Tue, 06 Dec 2011 11:16:36 GMT

Sorry! Use MonthNum in place of MonthKey.

Regards,

Jagan.

Problem with expression

Tue, 06 Dec 2011 11:45:58 GMT

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

Problem with expression

jagan — Tue, 06 Dec 2011 12:22:30 GMT

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.

Re: Problem with expression

Wed, 07 Dec 2011 12:13:06 GMT

Hi Jagan

I followed your suggestion and it now shows the month no (eg 12 for Dec) and the expression works for vMaxMonth

but not for vMaxMonth-1 so I'm back to square 1.

I'm attaching the model again - won't you please have a look if it's something I'm doing wrong in the expression please?

Regards

Danie