Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rsingh43
Contributor

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.

Tags (2)
7 Replies
MVP
MVP

Re: Two dates Filtering

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

YoussefBelloum
Esteemed Contributor

Re: Two dates Filtering

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

Re: Two dates Filtering

Hi,

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

Canonical Date

rsingh43
Contributor

Re: Two dates Filtering

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?

MVP
MVP

Re: Two dates Filtering

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....

rsingh43
Contributor

Re: Two dates Filtering

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

MVP
MVP

Re: Two dates Filtering

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

Community Browser