• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

7 day rolling average in Pivot Table

M

mrzoogle

Member
Hi everyone,

Hope you guys are doing great!

I was hoping if you could help me with this issue.

Basically I am trying to add 7 day rolling average into pivot table and can't find a correct way to do it.

Please find the attached spreadsheet where i've included the data I would like to include in pivot table.

If what I am trying to do is not possible could you let me know alternative solutions for this please?

Thanks in advance for taking your time for looking into this.

Kind Regards,

Z.
 

Attachments

  • 7day-average-pivot.xlsx
    342.2 KB · Views: 11
Hello Mate - Not sure if it can be done using pivot...some experts around may have a say on this...however, check the alternate solution....

Hope this helps...

Remember - Date column in your raw data must be sorted always i.e. "Oldest to Newest"
 

Attachments

  • 7day-average-pivot.xlsx
    364.4 KB · Views: 32
Hi Asheesh,

Thanks for taking your time in looking into this but unfortunately I am not looking for formula method.

I would like to have average calculations inside pivot table, even if it means doing the calculations in the rawdata as helper so that it can be called inside pivot table.

Currently I am not sure how to achieve that.

Thanks,

Z.
 
@NARAYANK991 Thanks for taking you time in helping me out. Your solution is perfect as always :)

I have added another column "client" and updated the formula you've provided and now the data is incorrect in pivot table.

Data is only correct when a particular client is selected, if I select all client the data is wrong.

Thanks for your time again.

@Asheesh thanks for solutions, yes I do use powerpivot sometime but still in learning process :)
 

Attachments

  • 7day-average-pivot.xlsx
    419.4 KB · Views: 17
Hi ,

See your file now ; this will work only for the following 2 cases :

1. No report filter is applied

2. A report filter is applied selecting only one client

If multiple clients are selected , there is no way to retrieve the selection without VBA ; at least that is my impression.

Narayan
 

Attachments

  • 7day-average-pivot_2.xlsx
    455.9 KB · Views: 39
Hi Narayank,

Thanks for the solution.

Could you help me understand why it is not possible when multiple clients are selected.

I want to apply this formula to around 40000 rows of data so I am thinking about pasting these as values once the calculations are done .

Sounds like this will cause problems, what would be more elegant solution on solving this?

This turned out to be more complicated than I thought :)

Thanks for taking your time.

Regards.
 
Hi ,

The reliable way would be to use VBA.

The report filter is in cell B1 in the sheet tab named pivot. When we use this cell reference in a formula , we can test for :

  1. The absence of a report filter , since the cell will contain the text (All)
  2. The selection of a single client , since the cell will contain the name of the selected client
Once you select more than one client , the cell displays only the text (Multiple Items) , but we are not able to retrieve the actual selections.

Using VBA this will be possible.

Narayan
 
Hi Narayank,

Thanks for help me understand this.

I am surprised that excel doesn't have this build in as it's quite straight forward calculation.

Will it be possible for you to help me/point me to the right direction for VBA solution please?

Kind Regards,

Z.
 
Hi ,

I think this is more complicated than it seems.

The problem with the approach we have taken is that we are not considering the client in the 7-day rolling average formula on the raw data tab. How to consider this is my problem.

To illustrate , put in yahoo as the report filter selection ; yahoo does not have any data on the first day of the raw data table 02-09-2014 ; however , the formula will not take this into account , and will display the result of the SUMIFS formula on 08-09-2014 , which is not really a 7-day rolling average. The first result should actually appear against 09-09-2014.

However this is just the tip of the iceberg ! What will happen if yahoo did not have data on any dates further down the data ? How is this presence of the client data on any date to be taken into account in determining which is the start date for a 7-day rolling average ?

Narayan
 
Hi Narayan,

Yes, it is quite a big problem. I didn't thought this could turn out to be this complicated.

What would you suggest? Am I the only one who's trying to get this into pivot table or has there been any other cases before?

Also what would you suggest we should do.

Thanks for your time.

Kind Regards,

Z.
 
Hi Narayan,

Would be great if you could get back on this when you have some time.

Also tagging @Hui so that he can add his expertise to this.

Hi Hui, hope you don't mind tagging you in this conversation.

I am struggling to find a solution for this and would be great if you could help me on this providing you have some time today.

Kind Regards,

Z.
 
Hi ,

I think it is beyond me ; the basic problem is getting the start date for each 7-day period ( on a rolling basis ) based on whether data for selected client(s) is available or not for the intervening days ; thus if in a 7-day period , the selected client(s) do not have data , then the 7-day period has to be extended to cover 8 , 9 ,... days.

The problem is that once the pivot table is formed , since there is only one entry per date , this calculation is easier ; without the pivot table , since there can be multiple entries per date , finding the start date is difficult.

My suggestion would be to form the summary table using dates as the row labels , introduce the additional column for calculating the rolling 7-day average , and form the pivot table from this summary table.

Narayan
 
Hi Narayan,

Thanks for getting back on this.

I am not sure if I follow your suggestion in the last paragraph.

Will it be possible for you to provide me an example please?

Kind Regards,

Z.
 
You must log in or register to reply here.
Back
Top