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.

Accepted answers

All comments

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.

Transaction_97103.PNG

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?

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…