compare multiple dates 1

[n2nuk]

I could really help with some advice on guidance on how I go about this

I have a set of assessment records that each have a date stamp.

what I need to find out is the number of days since the latest assessment and the number of days between previous assessments

for example
assessment date______days between
14/08/2014__________ 176 - compared to today's date
11/04/2014__________ 125 - compared to 14/08/2014
16/12/2013__________ 116 - compared to 11/04/2014
13/09/2013__________ 94 - and so on
21/06/2013__________ 84
20/05/2013__________ 32
25/02/2013__________ 84
21/11/2012__________ 96
22/08/2012__________ 91
22/08/2012__________ 0
22/08/2012__________ 0

Many thanks

 

[HowardHammerman]

First you need to sort and group the data
1) on the id field of the employee or project
2) on the filed containing the assessment date

Then create a formula like this:

If {table.idfield}<> previous({table.idfield}) then 0 else
{table.datefield}-{table.datefield}

You will probably need to make some adjustments.



Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[pmax9999]

Sort the records on Date in descending order, then create the following formula:

If      OnFirstRecord
Then    DateDiff("d", {Table.Date}, CurrentDate)
Else    DateDiff("d", {Table.Date}, Previous({Table.Date}))

If the {Table.Date} field is Date rather than Date/Time, you can subtract the dates rather than using DateDiff.

Hope this helps.

Cheers
Pete

 

[n2nuk]

Many thanks pmax9999 that works a treat!

I now have another problem which am hoping you may well know the answer to.

Each assessment has 10 questions that are marked 0-4. What I'd like to do is to total the 10 questions together to give me the total score.
If the score is under 10 then I want the assessment to be marked as Low, if the score totals between 10- 18 then Medium, and if over 18 then High.

the data looks like this

assessment_id_______assessment_date_______q1__q2__q3__q4__q5
1___________________01/01/2014___________2___1___4___0__
2___________________18/03/2014___________1___3___3___1___2
3___________________04/06/2014___________2___1___2___1___1

what I want my report to show is the total for each row so it looks like this

assessment_id_______assessment_date_______q1__q2__q3__q4__q5_____total___category
1___________________01/01/2014___________2___1___4___0__null_____7______Low
2___________________18/03/2014___________1___3___3___2___2_____11______Medium
3___________________04/06/2014___________2___1___2___1___1_____7_______Low

 

[pmax9999]

Try something like this:

WhilePrintingRecords;
Local NumberVar SCORE := {Table.Q1} + {Table.Q2} + {Table.Q3} + {Table.Q4} + {Table.Q5} + {Table.Q6} + {Table.Q7} + {Table.Q8} + {Table.Q9} + {Table.Q10} 

If	SCORE < 10
Then	'Low'
Else
If	SCORE < 19
Then 	'Medium'
Else	'Hgh'

Hoper this helps.

Cheers
Pete