How to convert MMM YYYY into normal date format DD-MM-YYYY

[gayatri7]

Hi All,

I have month field which is in 'MMM YYYY' format. I want to get the count of all records which is less the 'Jan 2016'. I am using below set expression but it is not working.

=Num(Count({Alt1<Month={"< Jan 2016 "}>} Distinct record_id), '##,##0') 

I assume it is not working because of Date format.

Could any one help on this please.

[tresesco]

How did you create the Month field in the script ? Did you use any date function or created as a string? If you have created it just concatenating multiple strings, 'not working' behavior is not unexpected. You should probably create the field using function like MonthName() or something similar ( Date( ....., 'MMM YYYY')). Could you post a sample qvw that demonstrates the issue?

[Anonymous]

is it a dual date?

if yes you could try:

=Num(Count({Alt1<Month={"<'Jan 2016'"}>} Distinct record_id), '##,##0')

try:

Num(Count({Alt1<Month={"<$(=date(date#('Jan 2016','MMM YYYY')))"}>} Distinct record_id), '##,##0')

[Anil_Babu_Samineni]

I am assuming, Instead of Month Field you may have similar field like Jan 2016 called Month_Year. Would you describe or show the Dimension field

[sunny_talwar · Accepted solution]

Is Month field a text or date field? Check these links

Why don’t my dates work?

Get the Dates Right

QlikView Date fields

and then you can check out this for dates in set analysis

Dates in Set Analysis

[gayatri7]

Below is my Month field created from [Vcto Recibo] field

Date(MonthStart(date#([Vcto Recibo],'DD-MM-YYYY hh:mm:ss')), 'MMM YYYY') as Month

[sunny_talwar]

Then try this

=Num(Count({Alt1<Month={"$(='<' & Date(MakeDate(2016, 1), 'MMM YYYY'))"}>} Distinct record_id), '##,##0')