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!

[MK_QSL]

Why don’t my dates work?

QlikView Date fields

[Anonymous]

Oliver, Try creating extra field monthnum and then in the sort of the table use that expression. use num(month(date))

[#4]

Sorry this is the correct image for Part 2 of my question as it shows the right column format

[OmarBenSalem · Accepted solution]

If you have a field in your source called "Expected Comp. Opp."for example written as follow:

31.05.2016

Do as follow:

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

then choose this dimension in your chart

LOAD

  //  "Expected Comp. Opp.",

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

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

With that being said, please try to learn about master calendar in Qlik

[#6]

Hi Shiva,

Oliver, Try creating extra field monthnum and then in the sort of the table use that expression. use num(month(date))

A couple of questions (sorry but I am a real "Dummie").

1. extra field monthnum - Do you mean "create a new column in your excel source sheet called Monthnum"?
2. the table use that expression - Do you mean "Import the Monthnum column from excel and then sort using the expression: =num(month(date))?

Thanks

[#7]

Thanks Omar, that's great and works well in the table.

Is there a way that I can now display all of the different Mar-2017s as a single bar in the chart?

Currently it displays like this:

I think the past you mentioned an expression for this but I was still trying to get my head around importing data correctly.

[OmarBenSalem]

What do you mean? Display all Mar-2017s?

[#9]

Hi Omar

Its easier to show how I would do it in Excel using a SUMIF formula:

The main difference being that the table I have shown here, does not have multiple instances of the same month because SUMIF added them all together and displayed them as 1 month.

Does that make sense? Sorry if I am not explaining it well.

[OmarBenSalem]

But you already are displaying all of Mar-2017 together in one bar.

And please go to your bar chart in Qlik Sense, and in 'tri' put your month field above: