Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
deepak_km9886
Creator
Creator

Extract year and month for the KPI

I have attached to files, how to get the  year and month like Jan 2018 .

I used the following ,  but i get hyphen as a result

yearmonth file is having date formats of kind yyyy-mm and YY/DD/M

Year(Date(date#(subfield([Invoice Date],' ',1),'MM/DD/YYYY')))

Year(Date#(YearMonth, 'YYYY-MM'))

Both dint yield any result

1 Solution

Accepted Solutions
petter
Partner
Partner

LOAD

   Date(Date#([Invoice Date'],'MM/DD/YYYY'),'MMM YYYY') AS YearMonth

FROM

  LIB://DATA/Invoice.xlsx (ooxml........ );

This will keep the underlying day of the month but display only the month and year part. If you want a date that always is the first of the month you will have to add MonthStart() like this:

   Date(MonthStart(Date#([Invoice Date'],'MM/DD/YYYY')),'MMM YYYY') AS YearMonth

LOAD

   Date( If( Index( YearMonth ,'-' ) , Date#(YearMonth&'-01','YYYY-MM-DD') , Date#(YearMonth, 'YY/DD/M') ) ,'MMM YYYY') AS YearMonth

FROM

  LIB://DATA/YearMonth.xlsx (ooxml.....);

Here you probably want the YearMonth to always start at the first of any given month:

   Date( MonthStart(If( Index( YearMonth ,'-' ) , Date#(YearMonth&'-01','YYYY-MM-DD') , Date#(YearMonth, 'YY/DD/M'))) ,'MMM YYYY') AS YearMonth

View solution in original post

3 Replies
ravic906
Creator III
Creator III

Hi!

Use Alt and MonthName functions together.

-RC

petter
Partner
Partner

LOAD

   Date(Date#([Invoice Date'],'MM/DD/YYYY'),'MMM YYYY') AS YearMonth

FROM

  LIB://DATA/Invoice.xlsx (ooxml........ );

This will keep the underlying day of the month but display only the month and year part. If you want a date that always is the first of the month you will have to add MonthStart() like this:

   Date(MonthStart(Date#([Invoice Date'],'MM/DD/YYYY')),'MMM YYYY') AS YearMonth

LOAD

   Date( If( Index( YearMonth ,'-' ) , Date#(YearMonth&'-01','YYYY-MM-DD') , Date#(YearMonth, 'YY/DD/M') ) ,'MMM YYYY') AS YearMonth

FROM

  LIB://DATA/YearMonth.xlsx (ooxml.....);

Here you probably want the YearMonth to always start at the first of any given month:

   Date( MonthStart(If( Index( YearMonth ,'-' ) , Date#(YearMonth&'-01','YYYY-MM-DD') , Date#(YearMonth, 'YY/DD/M'))) ,'MMM YYYY') AS YearMonth

renuka_sasikumar
Creator III
Creator III

Hi,

I used this

LOAD
[Invoice Date],
MonthName(Floor([Invoice Date])) as MonthName
FROM
[...\Testing\Invoice.xlsx]
(
ooxml, embedded labels, table is Sheet1);

I am getting the output as

Regards,

Renuka S