How do I get the dates in my bar chart in order of date?

[#1]

Hi

I want to import a sales funnel with lots of projects that have end dates in the DD/MM/YYYY format. 

I have already added another column of dates using the =text(date,"MMM-YY").

However I cannot get Qlik to either:

• Cluster the individual DD/MM/YYYY values into months OR
• Put the dates using the MMM-YY format, into date order

I don't know if the problem is solved in:

1. Excel formatting before data load
2. During the script execution for the load
3. In the dashboard app display settings to the right hand side of the dashboard in edit view

Any help would be appreciated.

[aarkay29 · Accepted solution]

instead of this text(date,"MMM-YY")

try this

Date(Monthstart(Date),'MMM-YY')

[#3]

Hi Aar Kay,

I did the date to text function in excel, not Qlik.

If I use Date(Monthstart(Date),'MMM-YY'), where do I use it?

As an expression to sort by?

Thanks

[#4]

This is my load script, "End Date" is the excel column with all the dates in DD/MM/YYYY format

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

    "Customer Area",

    "Market Segments",

    Division,

    "Opportunity ID",

    "Sales Stage",

    Prospect,

    Description,

    "Employee Responsible",

    "Exp. Sales Volume",

    "Win Probability (in %)",

    Status,

    "End Date",

    "Date Last Modification",

    "Opportunity Type",

    "Prospect ID",

    Competitor,

    Reason,

    "Source Details",

    "Sales Organization ID",

    "Sales Office",

    "Sales Group",

    "Distribution Channel",

    "Field of Application ID",

    "Sales Repr. ID",

    "Main Contact",

    Currency,

    Contact,

    Source,

    "Prod. Category ID",

    "Prod. Category descr.",

    "Sales Representative",

    "Start Date",

    "Type of Institution ID",

    Ind.Ch.Indicator,

    "Field of Application",

    "Market Subsegment",

    "Employee Responsible1",

    Region,

    PWP,

    "End Month",

    "Mod Month"

FROM [lib://Sales Funnel/CRM_funnel.xlsx]

(ooxml, embedded labels, table is CRM_P8C);

[aarkay29]

Yes

try that expression in sort by and see if it works.

In case if that does not seem to work then use that in your script like

Load *,Date(Monthstart(Date),'MMM-YY') as MonthYear

From

   Table;

Use this month year as your dimension

[#6]

Hi, sorry as a complete beginner, where and how would I put your suggestion into the load script?

Can you show me?

[aarkay29]

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

    "Customer Area",

    "Market Segments",

    Division,

    "Opportunity ID",

    "Sales Stage",

    Prospect,

    Description,

    "Employee Responsible",

    "Exp. Sales Volume",

    "Win Probability (in %)",

    Status,

    "End Date",

     Date(Monthstart([End Date]),'MMM-YY') as MonthYear,

    "Date Last Modification",

    "Opportunity Type",

    "Prospect ID",

    Competitor,

    Reason,

    "Source Details",

    "Sales Organization ID",

    "Sales Office",

    "Sales Group",

    "Distribution Channel",

    "Field of Application ID",

    "Sales Repr. ID",

    "Main Contact",

    Currency,

    Contact,

    Source,

    "Prod. Category ID",

    "Prod. Category descr.",

    "Sales Representative",

    "Start Date",

    "Type of Institution ID",

    Ind.Ch.Indicator,

    "Field of Application",

    "Market Subsegment",

    "Employee Responsible1",

    Region,

    PWP,

    "End Month",

    "Mod Month"

FROM [lib://Sales Funnel/CRM_funnel.xlsx]

(ooxml, embedded labels, table is CRM_P8C);

//Use this MonthYear as a dimension in your chart!!!

[MK9885]

//Assuming you are extracting Monthts from ENDDATE do a Preceding* load. like below, load statement before load statement. And then expression provided by Aar Kay like below.

Load *,

Date(Monthstart("End Date"),'MMM-YY') as MonthYear;

LOAD

    "Customer Area",

    "Market Segments",

    Division,

    "Opportunity ID",

    "Sales Stage",

    Prospect,

    Description,

    "Employee Responsible",

    "Exp. Sales Volume",

    "Win Probability (in %)",

    Status,

    "End Date",

    "Date Last Modification",

"Opportunity Type",

    "Prospect ID",

    Competitor,

    Reason,

    "Source Details",

    "Sales Organization ID",

    "Sales Office",

    "Sales Group",

    "Distribution Channel",

    "Field of Application ID",

    "Sales Repr. ID",

    "Main Contact",

    Currency,

    Contact,

    Source,

    "Prod. Category ID",

    "Prod. Category descr.",

    "Sales Representative",

    "Start Date",

    "Type of Institution ID",

    Ind.Ch.Indicator,

    "Field of Application",

    "Market Subsegment",

    "Employee Responsible1",

    Region,

    PWP,

    "End Month",

    "Mod Month"

FROM [lib://Sales Funnel/CRM_funnel.xlsx]

(ooxml, embedded labels, table is CRM_P8C);

[#9]

‌Hi

This worked ok for a table, I can the dates in order here.

however in the bar chart, the dates are still not sorting by date.

hence you get this kind of order: Sep-17 Jun-17 Jul-17 Aug-17 and so on.

am I being stupid?  I feel I am missing something?

thanks

[#10]

‌here is what I see