Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yashpace
New Contributor II

Count if Two Dates Match

Hi All

I am trying to count Distinct ORDNUM if NewOrderDate = NewdispatchDate, I am trying this in KPI and I want to select NewOrderDate from the Filter.

=IF (

[NewOrderDate] = [NewDispatchDate] ,  Count ( DISTINCT ( ORDNUM ) )

)

TIA!!!

2 Solutions

Accepted Solutions

Re: Count if Two Dates Match

Try this

Count(DISTINCT {<ORDNUM = {"=[NewOrderDate] = [NewDispatchDate]"}>} ORDNUM)

View solution in original post

Re: Count if Two Dates Match

May be this

Count(DISTINCT {<ORDNUM = {"=[NewOrderDate] = [NewDispatchDate] or Week([NewOrderDate]) = Week([NewDispatchDate]) or Year([NewOrderDate]) = Year([NewDispatchDate])"}>} ORDNUM)

 

View solution in original post

10 Replies

Re: Count if Two Dates Match

Try this

Count(DISTINCT {<ORDNUM = {"=[NewOrderDate] = [NewDispatchDate]"}>} ORDNUM)

View solution in original post

yashpace
New Contributor II

Re: Count if Two Dates Match

It's Working!

Thanks, Highly Appreciate!! 👍🙏

yashpace
New Contributor II

Re: Count if Two Dates Match

Hi,

I am trying to add Month(NewOrderDate) and week(NewOrderDate) filers in two new Filters and  

COUNT (Distinct ORDNUM)

If (Month(NewOrderDate) = Month(NewdispatchDate) or If(Week(NewOrderDate) = Week(NewDispatchDate))

I am trying following code - 

Count(DISTINCT {
<ORDNUM = {"=[NewOrderDate] = [NewDispatchDate]"}> +
<ORDNUM = {"=Week([NewOrderDate]) = Week([NewDispatchDate])"}>+
<ORDNUM = {"=Year([NewOrderDate]) = Year([NewDispatchDate])"}>
} ORDNUM)

 

Basically I am trying to Count ORDNUM(KPI) with three filters for the whole week and the whole month instead of just a single day.

 

TIA!

 

 

Re: Count if Two Dates Match

May be this

Count(DISTINCT {<ORDNUM = {"=[NewOrderDate] = [NewDispatchDate] or Week([NewOrderDate]) = Week([NewDispatchDate]) or Year([NewOrderDate]) = Year([NewDispatchDate])"}>} ORDNUM)

 

View solution in original post

yashpace
New Contributor II

Re: Count if Two Dates Match

Appreciate the help but it's not working - I will try to upload a QVDif that helps.

Re: Count if Two Dates Match

sure

yashpace
New Contributor II

Re: Count if Two Dates Match

Hi,

when I select month or week I get 0 in my KPI.

Thanks a ton!

Re: Count if Two Dates Match

There are multiple NewOrderDate for a single ORDNUM....

Capture.PNG

If this is how the data is, then you might need to create a new field which is a combination of ORDNUM and NewOrderDate in the script

yashpace
New Contributor II

Re: Count if Two Dates Match

Actually NewOrderDate is not a field in data, I created that from the calendar. It helps me because I just want to select one date/week/month from a filter and then want to count distinct ORDNUM where NewOrderDate = NewDispatchDate, NewOrderDate = NewProcessedDate, NewOrderDate = NewAddDate. 

This give me capability to select one date "NewOrderDate" and get distinct count of ORDNUM in all three KPIs(NewAdddate, NewDispatchDate, NewProcessedDate ).

 

I am trying to get how many orders were processed, shipped, added ton a given day. 

Update:- For each ORDNUM - NewAddDate, NEwDispatchDate, NewProcessedDate will never be different. NewOrderDate is just for filtering purposes- 

Thanks a ton!