52 weeks rolling, no access to data

[EhhMikey]

Hello all,

My company is currently building cubes for data purposes and I unfortunately cannot access the raw data in order to create a 52 week "Keep/Hide" Column. All I have is access to the Pivot table, I dont seem to be able to create a calculated field and most of the pivot commands I seem to have are pretty limited.

I need to create a rolling 52 week period that I wont have to go into every week and update the week #. Does anyone have any recommendations for doing this if you have the Pivot ONLY?
Thank you!!

Michael

 

[kalikj]

Michael,

This implies that the PIVOT output shows a "named date" e.g. 07-01/17 or similar rather than a relative reference such as Week 1, Week 2 ...etc?

The pivot table therefore comes back to you in an expanding format, i.e. each time you received it the table features additional columns corresponding to the new weeks?

I need to understand this in order to try and help you with your issue.

 

[EhhMikey]

Hi Kalikj,

I'm able to see the dates by year and FW, i.e. I can filter on 2006, 2007, etc and FW1, FW2, FW3 within the year. The first filter I show is Fiscal year, within that filter I can filter on FW.

Let me know if this helps!

Thank you!!

 

[kalikj]

Michael,

From what you have described, the Pivot table sounds as if it is already configured in such a way as to allow you to extract the data you are looking for?

Very difficult to comment further without seeing the actual table.

 

[EhhMikey]

Hi Kalikj,

Yes the data definitely has the data I need. I need to figure out a way to keep the FW week filters on a rolling 52 week period.

For instance, my range is currently FW22/2016 to FW21/2017. Every week I have to updated this, so next week I'd manually change the rage to FW23/2016 to FW22/2017 and so on. I do not have access to the data to create a "Hide/Show"column and filter on that. So I'm looking for an excel/VBA/Macro that will allow me to update the FW automatically without having to adjust the filters manually.

Please let me know if this is helpful.

Michael

 

[EhhMikey]

Hi Kalikj, reconnecting on this, is there any additional data I can provide? Thank you! Michael

 

[kalikj]

Hi Michael,

Still difficult to picture the layout of your PIVOT / to understand it's strucutre. It would be optimal if i could have a copy of this PIVOT featuring a small extract of sample data.