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

[#1]

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:

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

    

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

         

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.

    

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!

[#2]

Hi

Is "Tri" = "sorting" in English?

If so, then I did that and I still get multiple instances of the same month period on the X axis.

I have highlighted 3 instances of Sep-2015 below:

[OmarBenSalem]

Can you attach your QVF file?

[#4]

!

[OmarBenSalem]

I'll also need you excel file.

Thanks,

[#6]

Hi Omar,

Thanks for this!

I have taken out company critical information but you still have the date field and the value fields.

[sunny_talwar]

This should fix the issue of repeating month years

Date(MonthStart(Date#("Expected Comp. Opp.",'DD.MM.YYYY')),'MMM-YYYY') as MyMonth,

[OmarBenSalem]

It's working fine for me, Qlik Sense is already interpreting your dates as date fields; I think you were confused because the sorting was not right

See the attached App

[#9]

Sunny/Omar

Thank you guys so much for helping me.

I have a big task to do for my boss and you have helped me a great amount.

Many thanks!

I think I am starting to understand how Qlik works!

Oliver