topic Re: Function Date in QlikView

Function Date

Ofkoz — Wed, 25 Nov 2020 16:16:04 GMT

Hello gues,

Help me please.

I'm uploading the column in format:

01.01.2019;

03.01.2019

02.02.2019;

03.02.2019

How do I convert it to a format: January 2018;February 2019, with grouping by month and year.

Thanks.

column name - [End Date]

Re: Function Date

MindaugasBacius — Tue, 12 Mar 2019 08:40:08 GMT

,month([End Date]) & ' ' & year([End Date]) as [Year month]
Please read about Date and Time functions:
https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/DateAndTimeFunctions/date-time-functions.htm

Re: Function Date

Ofkoz — Tue, 12 Mar 2019 08:51:01 GMT

Thank you, but the values of January 2019 and February 2019 are not grouped. I get some data: January 2019;February, 2019;February, 2019;February, 2019; January 2019.

Re: Function Date

PV — Tue, 12 Mar 2019 09:24:49 GMT

Try creating a new column for your 'End Date' using MonthStart()/ MonthEnd() functions and use that new column as input for the solution suggested above.
MonthStart([End Date])
However make sure that it is not impacting the rest of your model.

Re: Function Date

olivierrobin — Tue, 12 Mar 2019 09:26:09 GMT

hello

you can use a preceding lod

first , load your data by adding a column year-month

then use a 2nd load with group by clause

Re: Function Date

MindaugasBacius — Tue, 12 Mar 2019 09:37:58 GMT

,month([End Date]) + year([End Date]) * 100 as [Year month]

You will come up with something like this 201901, 201902.

Re: Function Date

Ofkoz — Tue, 12 Mar 2019 10:53:52 GMT

The current script is.

//**********Table1************
LOAD Date([End Date],month([End Date]) & ' ' & year([End Date])) as [Year month],

Status // the histogram takes into account the Y-axis - Count(Status)

FROM
[path\name file.xlsx]
(ooxml, embedded labels);

Tell me how to use the second load to create an additional column and use GROUP by?