topic Re: How to convert MMM YYYY into normal date format DD-MM-YYYY in QlikView

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

gayatri7 — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

tresB — Mon, 10 Apr 2017 11:04:46 GMT

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?

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

Anonymous — Mon, 10 Apr 2017 11:04:50 GMT

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')

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

Anil_Babu_Samineni — Mon, 10 Apr 2017 11:06:54 GMT

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

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

sunny_talwar — Mon, 10 Apr 2017 11:14:15 GMT

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

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

gayatri7 — Mon, 10 Apr 2017 11:38:37 GMT

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

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

sunny_talwar — Mon, 10 Apr 2017 11:41:24 GMT

Then try this

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