topic Re: Find the difference between months in QlikView

Find the difference between months

Thu, 13 Sep 2012 04:06:44 GMT

Hi, good day everyone !

I want to get the number of months between 2 months. where

The expression I am using is

Month ([Expiry Date])-$(@calendarMonth) where Month([Expiry Date]) returns result like 'Aug' and $(@calendarMonth) returns result like 'July'.

Is it I need to convert the month string to number ?

Thanks sincerely for your help 🙂

Re: Find the difference between months

Sokkorn — Thu, 13 Sep 2012 04:12:24 GMT

Hi,

Try function NUM(). It look like NUM(Month ([Expiry Date]))

Regards,

Sokkorn

Re: Find the difference between months

Thu, 13 Sep 2012 04:25:59 GMT

Thanks Sokkorn Cheav, it works for the Expiry Date. How about the $(@calendarMonth) ?
I tried Num($(@calendarMonth)) but it does not return me 6 when I selected Jun.

Regards

Re: Find the difference between months

Sokkorn — Thu, 13 Sep 2012 04:28:29 GMT

Hi,

Can you give me the expression of this variable $(@calendarMonth)? Where you get it from? Or you can share your app.

Regards,

Sokkorn

Re: Find the difference between months

Thu, 13 Sep 2012 04:41:39 GMT

The expression for variable @calendarMonth is =GetFieldSelections(PeriodMonth).

It is from a list box selection. For example, when user select 'Jun', I need to pass 6 into my expression

Month ([Expiry Date])-$(@calendarMonth). Thus, the result will be 8-6 =2

Re: Find the difference between months

Sokkorn — Thu, 13 Sep 2012 04:48:22 GMT

Hi,

Your variable expression =GetFieldSelections(PeriodMonth)
will return string value something like 'Jun'. So you need to convert it into number with this expression @calendarMonth = GetFieldSelections(Match(PeriodMonth, $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))

Test it and let me know the result.

Regards,

Sokkorn

Re: Find the difference between months

Anonymous — Thu, 13 Sep 2012 05:34:38 GMT

hi ,

Sokkorn is right bt much easy way u can work is with following expression

date(date#(GetFieldSelections(PeriodMonth),'MMM'),'MM')

regards,

Vaibhav

Re: Find the difference between months

Thu, 13 Sep 2012 06:26:56 GMT

Hi,

I have changed the expression of @calendarMonth to =GetFieldSelections(Match(PeriodMonth, $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))

but variable $(@calendarMonth) returns nothing when the 'Jun' is selected in the listbox Calendar Month.

Regards,

Re: Find the difference between months

Sokkorn — Thu, 13 Sep 2012 06:40:13 GMT

Hi,

Is it possible to upload your app here?

Regards,

Sokkorn

Re: Find the difference between months

Anonymous — Thu, 13 Sep 2012 07:28:44 GMT

can u plz try

date(date#(GetFieldSelections(PeriodMonth),'MMM'),'MM')

n let me knw?

Re: Find the difference between months

Tue, 18 Sep 2012 09:45:43 GMT

Hi all,

Thanks for all of your helps.

I found the way suit to my work and it works.

I need to put this (Match('$(@calendarMonth)', $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))) in the calculation expression and for the variable, just set to @calendarMonth=GetFieldSelections(PeriodMonth) . It is not working by putting the Match expression together with the GetFieldSelections.

Thanks!