Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

bradedgar
New Contributor

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

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:

clipboard_image_0.png

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

 

4 Replies
Employee
Employee

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

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

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

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 

Digital Support
Digital Support

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

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
qliksus
Valued Contributor II

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

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