Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master III
Master III

Try

Num(Month(Date),'00')

Kushal_Chawda

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

alexandros17
Partner - Champion III
Partner - Champion III

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
Master III
Master III

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

Not applicable
Author

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
Master III
Master III

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

MarcoWedel

please post sample app.

regards

Marco

Not applicable
Author

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
Master III
Master III

Hi Lucille,

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

Regards

Sasi