16 Replies Latest reply: Mar 29, 2017 7:48 AM by Aehman K RSS

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

    Oliver Clarke

      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.

        • Re: How do I get the dates in my bar chart in order of date?
          Aar Kay

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

          try this

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

            • Re: How do I get the dates in my bar chart in order of date?
              Oliver Clarke

              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

              • Re: How do I get the dates in my bar chart in order of date?
                Oliver Clarke

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