Minimum Date Not Appearing in Group Footer 1

[Sange]

Hi, I have spent so much time trying different work arounds and have got no where so I am hoping someone will be able to assist me.

I am using CRv10.

My report is grouped by Order_Id and what I'm trying to do is work out the datediff between two program types/dates attached to each order. The programs can be quite varied and as a result, the data looks something like this:

Program Type Date
Induction 01/01/2010
CWK 05/01/2010
Counselling 03/03/2010
CWK 05/04/2010

What I'm trying to do is calculate the datediff between the first Induction date and the first community work program date. I initially created two formulas:

Formula 1 - Induction
if program_type = "Induction" then Date.Field

Formula 2 - Community Work
if program_type = "CWK" then Date.Field

The report then looked like this as a result:

Program Type Date Induction Community Work
Induction 01/01/2010 01/01/2010
CWK 05/01/2010 05/01/2010
Counselling 03/03/2010
CWK 05/04/2010 05/04/2010


I thought I could datediff between the minimum of these two formulas at the group level but the minimum date fields are actually appearing as blanks in the group footer which makes me unable to calculate the datediff.

I then tried running totals which worked well at displaying the minimum dates as well as calcuating the datediff however I also need to be able to count how many orders contained a datediff number of <= 14. I tried using a running total to count the order_ids meeting this criteria but keep getting that annoying "A running total cannot refer to a print time formula" message.

Is there another workaround someone else can suggest. I am at a loss at the moment.

Thanks so much.

 

[lbass]

The default to your conditional formulas is date(0,0,0) which appears as blank, so change your formulas to:

if program_type = "Induction" then
{Date.Field} else
date(9999,9,9)

Then for your group footer, use a formula like this:

If minimum({@Induction},{table.order}) <> date(9999,9,9) and
minimum({@CommunityWork},{table.order}) <> date(9999,9,9) then
datediff("d",minimum({@CommunityWork},{table.order}), minimum({@Induction},{table.order}))

If all orders have both programs, then you don't need to check for the present of 9/9/9999 dates.

Then to count the orders with a difference > 14, you should be able to insert a running total that does a distinct count of Order, evaluate using a formula:

minimum({@Induction},{table.order}) <> date(9999,9,9) and
minimum({@CommunityWork},{table.order}) <> date(9999,9,9) and
datediff("d",minimum({@CommunityWork},{table.order}), minimum({@Induction},{table.order})) < 15

-LB

 

[Sange]

It worked! Once again your assistance has been invaluable. Thank you so much lbass.