Difference between two dates (when matching another field)

[Daryn]

Hello,

Bit difficult to explain this one but,

I want to chart the difference in days, between dates. The two date's are in the same field but with a different flag (MvT).

The other factor is that all of these entries are related to a batch number, each batch number should have two entries, the Date In (MvT = 501) and the Date Out (MvT = 281) (there are a few exceptions which have more than one date in or out, due to poor master data that is being worked on, and for now will just skew the figures).

I would then chart it ideally by  Entry Date  (autocalendar week), or if not possible then by batch number.

Normally when charting difference in two dates I use;

AVG (Ceil(Interval(Date#([Date field1], 'DD.MM.YYYY') - Date# ([datefield2], 'DD.MM.YYYY'), 'D' )) )

I thought to make this work, I needed to perhaps create new separate date fields for the two MvT types, so in the load script I used;

Entry Date],
If(MvT = '281', [Entry Date]) AS [Date Out],
If(MvT = '501', [Entry Date]) AS [Date In],

then as my  measure,

avg (Ceil(Interval(Date#([Date Out], 'DD.MM.YYYY') - Date# ([Date In], 'DD.MM.YYYY'), 'D' )) )

I have attached example data of what I am using.

The Date In and Date Out look to be okay in a table and I have highlighted some of the data showing both dates against a batch in the below image. Not every batch will have both and again if it is possible I would like to show the days for those that do not have a Date Out (MvT  = 281) as the difference from the Date In (MvT =501) against 'today'.

My current results are blank as below if I use either Entry  Date or Batch as dimension.

Sorry, that's a headache to explain let alone work out!

Appreciate the time and effort anyone puts into looking or even reading this!

Regards as always,

Daryn

[henrikalmen]

I'm not certain that I completely understand what you want to do, but in the image where you have marked lines with yellow I think you'd want one row per batch and not two rows.

To achieve that you could join data in load script. Instead of having if-clauses creating Date In and Date out, do something like this to create a new table:

[dateInOut]: load distinct Batch resident [Entry Date];
left join([dateInOut]) load Batch, [Entry Date]) AS [Date In] resident [Entry Date] where MvT = '501';
left join([dateInOut]) load Batch, [Entry Date]) AS [Date Out] resident [Entry Date] where MvT = '281';

This may not solve your entire task but it's possibly a step on the way.

[Daryn]

I did very crudely, get this working using my load script values of;

If(MvT = '281', [Entry Date]) AS [Date Out],
If(MvT = '501', [Entry Date]) AS [Date In],

Then 'Batch' as the dimension and 'date((date([Date Out],'DD.MM.YYYY')-date([Date In],'DD.MM.YYYY')),'DD')' as the measure.

As mentioned, very crude!

So this shows me the number of days difference between booked in and out as such.

However, I would like the Batches with no Date Out to calculate against today's date if possible?

Thanks again for anyone who reads this or can assist.

Appreciated.

Daryn

[henrikalmen · Accepted solution]

In your formula, replace [Date Out] with alt([Date Out], today(1))
If [Date Out] is NULL or blank then today's date will be used instead.