Daily - Detailed View of Last 10 Days

mg3putt

Hi Vidas, I am hoping you can point me in the right direction here.

I got my migration to v3 working and realized that my favourite daily view (pivot table), the list of symbols over the last 10 days, is no longer contained in the workbook. Not a problem, I went into the Reports worksheet and created that pivot table on the Daily sheet. All good here.

But when I thought about it I really would like this pivot table in the light version of the workbook. When I attempted to create this pivot table I realized the data source in this workbook does not contain the "Days - Last 10" connection/field.

I have successfully created the Pivot Table by using the "Days - Last 30" connection/field and simply hiding the columns for the first 20 days.

Are you able/willing to provide me guidance as to how to get the "Days - Last 10" connection/field into the Light workbook? I have been playing around but Pivot Tables are not my forte (and googling has not helped me solve this).

I understand that by modifying the workbook it likely puts me in an unsupported environment.

Let me know. And thank you....Mark

jwenga

Hi mg3putt,

I was wondering if you would share the steps you used to recreate the "Days - Last 10". I also enjoyed using that table on a regular basis.

Thanks...

VidasMatelis

You can make adjustments to model if you have PowerPivot menu available. Not all SKUs (versions) of Excel have PowerPivot menu that allows changes.
Go to menu PowerPivot->Manage and select table Dates.
Select cell very right: "Add columns" and then at the top add formula:

=IF(AND([QuotesExists], [Date]>=MINX(TOPN(10, CALCULATETABLE(Dates, Dates[QuotesExists], All(Dates)), Dates[Date]), Dates[Date])),"Yes","No")

It might show error sign, but do right mouse click and choose "Calculate".

Right mouse click on this new column header, choose "Rename column" and enter the name "Days-Last 10"

If you do not have PowerPivot menu, your option is to use "Days-Last 30" column

jwenga

Getting this error

VidasMatelis

Based on the screenshot, you are getting this error because you are trying to add new column to "Report" table, but you first have to switch to "Dates" table and then add column!!!

mg3putt

Thanks Vidas, I will try this in the next couple of days.

mg3putt

Thanks Vidas....got it working. Was pretty simple given your instructions. Mark