Average on text field count.

[MrRedLi]

hi,

hope some one can help me with this. I have 3 feilds in my table;

EndDate = date field
Time = time field
ID = String field
what i am tring to achive is count for each of the week for last 30 days from the date selected by the user. and then give average for those days based on count of ID for that day of the week.

I am using formula @monday to get count of ID for monday and place this under monday if the date is Monday.

IF DayOfWeek({Table.EndDate} ) = crMonday
THEN Count ({Table.ID}, {Table.EndDate}, "daily")

I am also doing group on Time to show me the break down in hours and the second group is date.

however this is what my problem is, i can not an average on the number of ID for day. Can some one point me in right direction:

Time Slot-----Monday--Tuesday--Wednesday--Thursday--Friday--Saturday--Sunday
6:00am----------------------------------------------------------------------------------------(this is what i can not get - the answer here should be 2 for thursday and 5 for friday)
--1/01/2015 --------------------------------------------2
--2/01/2015 ---------------------------------------------------------5
--8/01/2015 --------------------------------------------2

 

[Charliy]

You have a formula for each day of the week to give you the count.

Create another set of formulas (or Running Totals) to count the number of Mondays, Tuesdays, etc, then divide the count for Monday by the number of Mondays, and so on.

 

[MrRedLi]

Hi Charliy,

I can not use the count formula in the time slot as it does not count all the ID for the day. for example if i had 2 monday in time slot of 6am each of 2 id then the count formual is giving me answer of 2 instead of 4.