Average of Every Monday

SwathyShankar

Hi Team,

I have to find Average of particular Column based on the DATE .

 

(i.e.)

• To find How many Subjects have been Enrolled in a Particular Site?
• To find Average of subjects enrolled per week, we are calculating Every Monday(We used WEEKDAY function to get a first day of the week.)

Can any one help me with this?

 

Thanks.

Ingilavicus

Hi @SwathyShankar ,

 

I see this question in two ways:

1.  You have the first step using the weekday() function. I would continue by creating a list of weekdays Monday to Sunday with the codes (1-7). Then convert the weekday number that you get from the formula to text and use a finditem() to find the corresponding day from the Weekday list. 

Now with weekday represented as a list, you should be able to do the necessary calculations. (If a weekday is important in that sense)

 

2. If you have daily data and on each Monday you want to calculate the average enrolment rate you could use the cumulate() function. Use the weekday() outcome as a boolean "Weekday(Date) = 1" to reset the weekly value when needed and divide by 7. Depending on which days you want to take into account, you might also use previous to pull the cumulated number up till Sunday when calculation the average of last week. 

 

Hope this helps to think of something that works out. 

 

Andirs

SwathyShankar

Hi thanks for your response.

Actually I missed one thing. Using WEEKDAY function I've found Date's of Monday.

The thing is that I want to find the average of Subject enrolled in the Site in a particular Date.

FYI (Subject and Site is a Line Item(not a List))

Ingilavicus

Maybe you can share a screenshot of what you are trying to accomplish. Otherwise, I don't completely understand the issue. 

 

SwathyShankar

Hi ,

You can refer the below Screenshot.

 

Need to SUM the 'COUNT' Column Group by 'Date' and 'Site' Column.

 

Ingilavicus

Ok if I understand correctly, you should just use a simple sum formula for this. If you don't have the sites as a list already you'll have to make that. And add a new item in the view that is formatted as this site lists and uses the FINDITEM function. Then you can make another module where you have sites and Time as dimensions and just use a formula like this:

COUNT[SUM: SITE LIST, SUM: Date]

 

If you want to pill back the counts in the Transactions view use a lookup:

 

But then this doesn't look at your original question of the weekday parameter.

 

Curious if this is what you were looking for?