OTIF based on two date fields and a count

[Daryn]

Hi, 

Sorry to trouble the experts ... again ...!

I am trying to work on an expression to create a chart for OTIF (On time in full).

I have two date fields. [WADAT] (planned issue date) and [WADAT_IST] (actual issue date). 

I have the Dimension as the 'month' view date from the same field.

I want to show the amount of orders [VBELN] that were;

EARLY      -       WADAT_IST < WADAT

ON TIME -       WADAT_IST = WADAT

LATE         -       WADAT_IST > WADAT

I have tried variants of:

count (IF ( [WADAT_IST] < [WADAT], VBELN ))             

Changing the < for = and > for the other measures.

and

count({<[WADAT_IST] = {" < $(=Date([WADAT]))"}>} distinct [VBELN])     

Again changing  the < for = and > for the other measures.

But here [WADAT] is not picked up as a field, just text assume this is my "" in the wrong place.

I get nothing from any of the above in the way of charted results?

Is this the right way to go about it, is there a better angle to take?

Thanks in advance for any hints/tips/resolution.

Regards, Daryn.

[Or]

Your first approach, using the if(), should work assuming both fields are valid dates. What problem exactly are you encountering with it? Could you provide some sample data and expected results?

[Daryn]

Image above shows results from using the count - if expressions. 

I am also using these fields in other charts and they are working as expected (as dates etc).

I am hoping to see results similar to another chart that I use (below), using the ERDAT field but with different measures based on a completion status.

The data source is a SQL DB, added via load script. Not sure if I can get sample data, but will look this afternoon.

Thanks for the response, appreciated.

Regards, Daryn

[Or · Accepted solution]

I would suggest confirming your fields are valid dates. It looks like they might be strings based on the bottom image, not dates.

[Daryn]

Sometimes it just takes another pair of eyes to point out the obvious.

Thank you, WADAT_IST had'nt been picked up as a date!

Now to just pick the best looking chart type. But the data is now there.

Really appreciate the time and help.

Regards Daryn