topic Extract year and month for the KPI in Visualization and Usability

Extract year and month for the KPI

deepak_km9886 — Mon, 30 Jul 2018 07:06:26 GMT

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

Re: Extract year and month for the KPI

Anonymous — Mon, 30 Jul 2018 07:16:31 GMT

Hi!

Use Alt and MonthName functions together.

-RC

Re: Extract year and month for the KPI

petter — Mon, 30 Jul 2018 07:32:50 GMT

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

Re: Extract year and month for the KPI

renuka_sasikumar — Mon, 30 Jul 2018 07:34:54 GMT

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