topic Re: Month Name to Month Number in QlikView

Month Name to Month Number

Thu, 23 Jul 2015 13:29:04 GMT

Hi guys,

I have 2 fields that I would like to compare to each other.

The one field is a period in the format YYYYMM and the other field is a date YYYY-MM-DD.

I would like to use an expression like

if(year(date)&month(date) = period, 1, 0)

but it spits out the month(date) as 'Jan' and not '01'...

how can I fix this?

Thank you

Re: Month Name to Month Number

antoniotiman — Thu, 23 Jul 2015 13:33:51 GMT

Try

Num(Month(Date),'00')

Re: Month Name to Month Number

Kushal_Chawda — Thu, 23 Jul 2015 13:37:35 GMT

try,

if(date(DATE_Field,'YYYYMM')=period,1,0). it will be better to use this condition in script.

then on front end use this flag

Re: Month Name to Month Number

alexandros17 — Thu, 23 Jul 2015 13:44:10 GMT

Let's supose that the field with the format YYYYMM is FLD1 and the second field (YYYY-MM-DD) is FLD2 then

If(Left(FLD1,4)*100+Right(FLD1,2) = Year(FLD2)*100+Month(FLD2) ,1 ,0)

Let me know

Re: Month Name to Month Number

sasiparupudi1 — Thu, 23 Jul 2015 13:52:51 GMT

if (Date(Date#(date,'YYYY-MM-DD'),'YYYYMM')=period,1,0)

Re: Month Name to Month Number

Thu, 23 Jul 2015 14:19:15 GMT

Thank you, this worked well

but now my next dilemma comes in when I try to do this calculation in a textbox... I have created a variable, that calculates the max period and only reflects the last month's values, except if you choose another period:

This is my list box equation....

=num(count({<PERIOD = {$(vPeriod)}, Date(Date#(ACCCLOSEDT,'YYYY-MM-DD'),'YYYYMM') = {$(vPeriod)} >} DISTINCT ACCNUM), '# ##0', '.', ' ')

and this is my variable equation:

=if(Left(GetFieldSelections(PERIOD),3) = 'NOT',
max({<PERIOD-={$(=SubField(GetFieldSelections(PERIOD),'NOT ',2))}>} PERIOD),
max(PERIOD))

But it is not working... any thoughts?

Re: Month Name to Month Number

sasiparupudi1 — Thu, 23 Jul 2015 18:25:41 GMT

If your $(vPeriod) returns a valid period then may be you can try the expression

in parts..

=num(count({<PERIOD = {"$(vPeriod)"}} >} DISTINCT ACCNUM), '# ##0', '.', ' ')

I am doubtful if you can use like this in the set analysis , Date(Date#(ACCCLOSEDT,'YYYY-MM-DD'),'YYYYMM') = {$(vPeriod)}

HTH

Re: Month Name to Month Number

MarcoWedel — Thu, 23 Jul 2015 19:56:13 GMT

please post sample app.

regards

Marco

Re: Month Name to Month Number

Fri, 24 Jul 2015 05:47:33 GMT

I managed to sort it out, but moving the expression to the script..

I made a period of my date there instead of trying to do in the calculations...

thank you for all of your help!!!

Re: Month Name to Month Number

sasiparupudi1 — Fri, 24 Jul 2015 05:55:29 GMT

Hi Lucille,

Its good to know that you solved your problem. Please close this thread

Regards

Sasi