Number of days between cases

[#1]

Hi There

I'm struggling to calculate the number of days between two dates for a given criteria. The  expression I am using is:

=count({$ <State = {'Open'},SubmitDate={">=$(Date(varMinDate))<=$(date(varMaxDate))"}>} [CR ID])

I'm trying to calculate the number of days that my oldest case is open.

vMinDate and vMaxDate are calculated using the following code during the load process

LOAD

  Min(Date(Date# (SubmitDate , 'DD/MM/YY'),'DD/MM/YY')) as MinDate,

  Max(Date(Date# (SubmitDate , 'DD/MM/YY'),'DD/MM/YY')) as MaxDate

  //Num(Date#(Min(SubmitDate), 'DD/MM/YYYY'))as MinDate,

  //Num(Date#(Max(SubmitDate), 'DD/MM/YYYY')) as MaxDate

RESIDENT [Facts];

//EXIT script

//--- Assign the start and end dates to variables

LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');

I can't seem to get this to work. Can anyone help point me in the correct direction please?

[marcus_sommer]

You should use a trace-statement like: trace '$(vMindate)'; to check if your variables do work and which value and format they contain. Further your expression should be rather look like:

=count({$ <State = {'Open'},SubmitDate={">=$(=Date(varMinDate))<=$(=date(varMaxDate))"}>} [CR ID])

- Marcus

[#3]

HI Marcus,

Thanks for the reply and apologies for not getting back sooner. I managed to work this out using the following expression:

=Num($(vToday)-(MinString( {$<State ={'Open'}, SubmitYear =, SubmitMonth =  >} [SubmitDate])),'#,##0')

This basically allows me to calculate the number of days between today and the oldest case in the database

Cheers,

John