Count Expression with Condition to Calculate On Time To Promise (Date Received - Promise Date < 0)

[bradedgar]

Hey Guys,

I can't seem to find a good answer to the below.  Here is the result that I'm looking for in a Pivot Chart:

The dimensions I've added to my pivot chart are

• Supplier Name (Row)
• PO Receipt Month (Column)

In my head here is how the calculation looks (which isn't working of course ad that's why I am here):

= count([PO Receipt Date] - [PO Promise Date] < 0) / count([PO Number])

What I'm looking to calculate right now is basically the following (# of lines received on time / total # of lines received).

Here's the breakdown

• To get the total # of lines received: I want the count of the total # of lines received that had a negative value for ([PO receipt date] - [PO Promise Date]).
• I then want to get a count of total # of lines.  Right now I'm just using the PO Number which is counting exactly how many total lines were received in that month.
• I do want to make sure that the Receipt Month will restrict and only show lines that are received however. I am assuming that this will do this as part of the dimension.

I hope this makes sense.  Looking at this to build a supplier scorecard in one of our apps.

Thanks for the help

[Lisa_P]

The best way to do this is to do the calculation as you load the data, create a new field [PO Delta] by subtracting the fields as they are loaded. Then it is easy to count how many are < 0 in your expression.
Count({<[PO Delta]={"<0"}>}[PO Number])/count([PO Number])

[bradedgar]

Thanks Lisa.

I thought about doing the same but the app is built by our BI team on a qlikview server and I don’t have access to the load(runs in batch every 12 hours) . I had thought about doing that way as well.

Any other thoughts?

Thanks

brad 

[Brett_Bleess]

Brad, you may want to consider attaching your QVW or an example one, as I think folks may need more details related to the data model etc. in order to potentially come up with other ideas here.

Regards,
Brett

[qliksus]

Whats the issue you are getting with the below expression ?  Do attach a sample file 

= count( {< [PO Number]= { "=[PO Receipt Date] - [PO Promise Date] < 0" }  >}   [PO Number] ) / count([PO Number])