Count if Two Dates Match

[yashpace]

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

[sunny_talwar · Accepted solution]

Try this

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

[yashpace]

It's Working!

Thanks, Highly Appreciate!! 👍🙏

[yashpace]

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!

[sunny_talwar · Accepted solution]

May be this

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

[yashpace]

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

[sunny_talwar]

sure

[yashpace]

Hi,

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

Thanks a ton!

[sunny_talwar]

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

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]

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!