How to create a calendar with multiple Date formats??

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

Hello Experts,

How Can I create a calender filter(Year,month and Quarter) using different date formats from two fields.

Attached is the source data.

Thanks in advance.

Re: How to create a calendar with multiple Date formats??

raman_rastogi — Tue, 06 Nov 2018 12:05:59 GMT

Hi Vijaya,

Try This

Before use Change File Path.

Data:

LOAD

Country,

Product,

Date("Mf Date",'DD/MM/YYYY') as Com_Date,

Cost as Value,

'Cost' as Flag

FROM [lib://DateData/SampleDate_data.xlsx]

(ooxml, embedded labels, table is Cost);

Concatenate

LOAD

Country,

Product,

Date("Sale Date",'DD/MM/YYYY') as Com_Date,

Sale as Value,

'Sale' as Flag

FROM [lib://DateData/SampleDate_data.xlsx]

(ooxml, embedded labels, table is SALE);

CalendarMaster:

LOAD

Date(InvoiceDate) AS Com_Date,

Year(InvoiceDate) AS Year,

'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,

Month(InvoiceDate) As Month,

Day(InvoiceDate) As Day, Week(InvoiceDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min(Com_Date) AS MinDate,

Max(Com_Date) AS MaxDate

RESIDENT Data;

Regards

Raman