17 Replies Latest reply: Apr 5, 2017 2:22 PM by Oliver Clarke RSS

    Getting great help but still struggling with fundamentals of importing dates correctly

    Oliver Clarke

      Hi

       

      Thanks to all the very clever people who have helped me so far.  However I am still very much struggling as I don't think I have given enough context to my date import requirements.

       

      So here goes! This is a long thread due to images

       

      1. My raw data

       

      Its in an XLSM file and the data in coming from an SAP Business Warehouse sheet:

       

      1Capture.JPG

       

      2. What do I want to do in Qlik Sense?

       

      In short, I want to group all of the individual dates into their respective months. This means that 29.01.15 would become Jan-15 and 06.02.17 would become Feb-17, and so on.  Because I often want to use bar charts to display sum values for measures by month, I need all of the Jan-15s to be the same as well as all the Feb-17s and so on. Otherwise I get multiple Jan-15s and Feb-17s along the X-Axis of my charts.

       

      Currently I use a two step transformation in the excel file to get a new column in source sheet.

       

      1. First I do a text to column function in excel to convert 06.02.17 into 06/02/17

           3Capture.JPG

      2.     Then using my newly formatted dates, I make a new column called "End Month" and use a date to text formula:

       

                4Capture.JPG

      3.     I am pretty sure that when I load this new column "End Date" into Qlik, Qlik does not convert to a date that can be sorted properly in a chart or table.

       

           2Capture.JPG

      You can see this in the Qlik Sense dashboard above, the dates do not actually sort as dates because they are actually text.

       

      So!, if this is my load script (below), my question is what script function to I need to convert to a proper MMM-YY date format that will work in Qlik, and where should it go in the script?

       

      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",

          "Opportunity ID",

          "Customer Area" as "Sales Area",

          "Market Segment" as Sector,

          "Org Division" as Division,

          Prospect as Customer,

          F10 as ERP,

          "CRM Product category" as Product,

          "Employee Responsible" as "Account Manager",

          Phase,

          Status,

          "Win probability (%)",

          £ as "Opp Value",

          Region,

          "End Month",

          PWP

      FROM [lib://Dashboard/Dashboard Workbook.xlsm]

      (ooxml, embedded labels, header is 1 lines, table is [P8C Funnel]);

       

       

      I understand that the answer lies in the script import and also the use of set analysis, its just that I am not strong enough on the fundamentals to pull all this together with out spelling out in the way I have above.

       

      I hope this helps!

       

      Thanks, Oliver!