Two dates Filtering

[Anonymous]

Hi,

I am having some trouble working out how to create a filter on two different dates.

I currently have one script in my data load editor which contains CreatedDate and DispatchDate. These dates refer to when an order was created and when the order was dispatched. An order could have been created in 2017 or earlier and dispatched anytime after the created date.

Using these dates I have created summaries by month/year of orders created and orders dispatched:

App expression for MonthYear:

=date(FLOOR(MonthEnd(CreatedDate)),'MMMYYYY')

=date(FLOOR(MonthEnd(DispatchDate)),'MMMYYYY')

=count(distinct OrderID)

MonthYear (CreatedDate)	OrdersCreated
Nov-2017	10
Dec-2017	15
Jan-2018	5
Feb-2018	20

MonthYear (DispatchDate)	OrdersDispatched
Nov-2017	5
Dec-2017	2
Jan-2018	15
Feb-2018	10

How would I go about creating a year filter for the above tables in Qlik Sense?

Thank you in advance for any help.

[sunny_talwar]

I think you should create a Canonical Date in this case.

[YoussefBelloum]

Hi,

if you're looking to create separated Year dimensions to use it on the charts, you can use Year() function.

Year(date(FLOOR(MonthEnd(CreatedDate)),'MMMYYYY'))

[md_qlikview]

Hi,

Try using Canonical Calendar if you want to filter using single common field

Canonical Date

[Anonymous]

Thanks for the feedback guys.

I have had a read about Canonical Date but not sure if this works in my case, as I have one script in data load editor but then create two tables in NPrinting.

Script:

Orders table = contains CreatedDate and Dispatch Date

NPrinting:

Table 1 = CreateDate, Orders

Table 2 = Dispatch Date, Orders

Does Canonical date not merge two separate tables in the data load editor?

[sunny_talwar]

They don't merge the table, but just use the dates from the two tables in a bridge/link table to create a master calendar which can be used for either of the dates....

[Anonymous]

So can I create two separate scripts (1.CreatedDate, 2.DispatchDate) in the data load editor and then follow Canonical date?

[sunny_talwar]

So can I create two separate scripts (1.CreatedDate, 2.DispatchDate)

You mean two separate tables, right? If that is what you mean... then yes... Else, I am not sure what you mean when you say two separate scrips?

then follow Canonical date?

Yes