How do I change column of different dates (dd-mm-yyyy) into (mmm-yy)?

[#1]

Hi,

I have a data set from a CRM sales funnel that has project end dates in the full date format.  I want to show these dates clustered into a "MMM-YY" format to make a bar chart in the dashboard.

I am a novice and not a developer.

What is an easy way to do this?

I have highlighted in red text the field that will load the project end dates.

Thanks!

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

SET CreateSearchIndexOnReload=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LOAD

    "Expected Comp. Opp." as "End Date",

    "Customer Area",

    "Market Segment",

    "Org Division",

    F5,

    "Opportunity ID",

    "Employee Responsible",

    Prospect,

    F9,

    "CRM Product category",

    Status,

    "Win probability (%)",

    £

FROM [lib://Qlik/master_data.xlsx]

(ooxml, embedded labels, header is 1 lines, table is crm_funnel);

[sunny_talwar · Accepted solution]

You can try using MonthStart() function

Date(MonthStart("Expected Comp. Opp."), 'MMM-YYYY') as "End Month Year",

[#3]

Hi Sunny, thanks!

How would I put this into the script? (sorry I really am new to scripting).

Thanks!

[sunny_talwar]

Like this

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

SET CreateSearchIndexOnReload=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LOAD

    "Expected Comp. Opp." as "End Date",

    Date(MonthStart("Expected Comp. Opp."), 'MMM-YYYY') as "End Month Year",

    "Customer Area",

    "Market Segment",

    "Org Division",

    F5,

    "Opportunity ID",

    "Employee Responsible",

    Prospect,

    F9,

    "CRM Product category",

    Status,

    "Win probability (%)",

    £

FROM [lib://Qlik/master_data.xlsx]

(ooxml, embedded labels, header is 1 lines, table is crm_funnel);

[#5]

Awesome thank you!