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: 
Anonymous
Not applicable

Two dates Filtering

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-201710
Dec-201715
Jan-20185
Feb-201820

MonthYear (DispatchDate)OrdersDispatched
Nov-20175
Dec-20172
Jan-201815
Feb-201810

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

Thank you in advance for any help.

7 Replies
sunny_talwar

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

YoussefBelloum
Champion
Champion

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
Creator II
Creator II

Hi,

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

Canonical Date

Anonymous
Not applicable
Author

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
Not applicable
Author

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