Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Month Name to Month Number

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

9 Replies
antoniotiman
Honored Contributor III

Re: Month Name to Month Number

Try

Num(Month(Date),'00')

Re: Month Name to Month Number

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

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

sasiparupudi1
Honored Contributor III

Re: Month Name to Month Number

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

Not applicable

Re: Month Name to Month Number

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?

sasiparupudi1
Honored Contributor III

Re: Month Name to Month Number

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

please post sample app.

regards

Marco

Not applicable

Re: Month Name to Month Number

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!!!

sasiparupudi1
Honored Contributor III

Re: Month Name to Month Number

Hi Lucille,

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

Regards

Sasi

Community Browser