Cumulative Totals

[janedane]

Hi,
I am having trouble with cumulative Data. I have two fields, a treatment date and a return to work date. To work out the difference between the two I use DateDiff
Return to Work:

Datediff("D",{Table1.Treatment Date},{Table1.Date returned to Work})

To Divide them in One month, Three months, Six months, One Year and Over one Year... Which return a True or False Value
One Month: {@Return to Work} < 31
Six months : {@Return to Work} in [93 to 186]

ETC...

When I try to do a count on {@one month} it counts all the records even if I use the following formula. I just want it to count where One Month = True

if {@One Month} = true
then count({@One Month}


The reason I am doing it this why is so that I can have Cumulative totals i.e.

Three Months = count({@One Month})+count({@Three Months})

Any suggestions or comments would be greatly appreciated

Many thanks,
Jane

 

[synapsevampire]

Try different formulas:

If Datediff("D",{Table1.Treatment Date},{Table1.Date returned to Work}) < 31 then
1
else
0

And use a sum.

You can also use:

{Table1.Date returned to Work}-{Table1.Treatment Date}

-k

 

[janedane]

Hi,
Thanks for the suggestion but it's the count() I am using that doesn't work.

Even when I change it to

If Datediff("D",{Table1.Treatment Date},{Table1.Date returned to Work}) < 31 then
1
else
0
This Still counts all the regards of the 1 or 0
if {@One Month} = 1
then count({@One Month}

When I tried Sum{@One Month}
I got a completely different answer.

Thanks,
again,
Jane

 

[janedane]

Hi, Thanks for your help but I figured this one out.

Instead of adding the count for {@one month} etc..

I have created running totals and added them instead.

Thanks,
Jane