Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kvr9
Creator
Creator

How to create a calendar with multiple Date formats??

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.

1 Reply
raman_rastogi
Partner - Creator III
Partner - Creator III

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