topic Re: Count Expression with Condition to Calculate On Time To Promise (Date Received - Promise Date &l in QlikView

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

bradedgar — Mon, 16 Sep 2019 19:20:56 GMT

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

Re: Count Expression with Condition to Calculate On Time To Promise (Date Received - Promise Date &l

Lisa_P — Mon, 16 Sep 2019 23:31:01 GMT

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])

Re: Count Expression with Condition to Calculate On Time To Promise (Date Received - Promise Date &l

bradedgar — Tue, 17 Sep 2019 01:07:20 GMT

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

Re: Count Expression with Condition to Calculate On Time To Promise (Date Received - Promise Date &a

Brett_Bleess — Thu, 26 Sep 2019 20:42:59 GMT

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

Re: Count Expression with Condition to Calculate On Time To Promise (Date Received - Promise Date &l

qliksus — Fri, 27 Sep 2019 03:56:05 GMT

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])