How to create a calendar with multiple Date formats??

[kvr9]

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.

[raman_rastogi]

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