4 Replies Latest reply on Aug 22, 2018 1:57 AM by Frederique Bordignon

# Daily average over a period of time

Hello,

I would like to calculate the average number of tweets from different Twitter accounts over a period of time even if nobody has tweeted on specific days and even if everybody has not tweeted everyday.

My data are like this :

I would like to know the daily average number of tweets for each Twitter accounts. I expect to get 1,2 (12/10) for Twitter account1 who has tweeted 12 times over those 10 days, even if nobody has tweeted on 05/01/2010.

And, if I use a date filter and want to know the daily average of tweets between 06/01/2010 and 08/01/2010, I expect to get :

- for Twitter account1 : (2+1+2)/3 = 1,7

- for Twitter account2 : (0+0+1)/3 = 0,33

- for Twitter account1 : (2+2+0)/3 = 1,33

I did not manage to calculate the average on the basis of a fixed number of days. The number of days vary according to the number of days each Twitter account has tweeted -> for example, if a Twitter account has only tweeted once in a year, its average is 1...

I would appreciate if I can get some help on that problem.

Thanks

Frederique

• ###### 1. Re: Daily average over a period of time

Don't filter.  Use two parameters:  [Start Date] and [End Date] (or whatever you want to name them.)

Let the user specify your start and end.  Do a DATEDIFF between the two values to determine the number of days in question.

Make a fixed LOD to count the number of tweets for each account:

{ FIXED [Twitter account] : sum( if [Date] >= [Start Date] and [Date] <= [End Date] then 1 else 0 END ) }

Divide the LOD by the datediff+1 and you have your average for each account.

• ###### 2. Re: Daily average over a period of time

Dear Joe,

It does not work assuming I did properly what you told me: the number of days still vary according to the Twitter account, but it should be the same for everyone.

Thanks...

Frederique

• ###### 3. Re: Daily average over a period of time

See attached.

What you had in the calc [Days] was really the number of tweets.  I renamed that to [Tweets].

I made an entirely separate calc called [Days] that does the DATEDIFF (plus 1).  That number should always be uniform along all twitter accounts.

Then I modified the [datediff] calc to do tweeds divided by days.  It really shouldn't be called datediff.  It's really average tweets, but the name doesn't really matter.  it's the syntax that's important.

So now play with it and see how it works.

1 of 1 people found this helpful
• ###### 4. Re: Daily average over a period of time

Thank you Joe. This time this is perfect and I eventually managed to adapt the solution to my real data.