Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Master III
Master III

Try

Num(Month(Date),'00')

Highlighted
MVP
MVP

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

Highlighted

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

Highlighted
Master III
Master III

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

Highlighted
Not applicable

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?

Highlighted
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

Highlighted

please post sample app.

regards

Marco

Highlighted
Not applicable

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

Highlighted
Master III
Master III

Hi Lucille,

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

Regards

Sasi