Pivot tables - sold "per week" but target only "per month"

[alexander starbuck]

Hello everybody!

I have a (probably) very simple question or two, regarding pivot tables. First, here is an example of the document I am working with - DOWNLOAD LINK

My questions are:

1. What is "good practice" if you have MONTHLY targets but WEEKLY performance tracking? (Split targets equally by weeks or simply put total monthly target into last week of that particular month)
   
   
2. Why do CALCULATED FIELDS in pivot tables not accept "n/a" string as option in IFERROR()? Example: IFERROR('sales'/'targets', "n/a")
   
   
3. Is it possible to use only ROW TOTAL for one of the pivot table fields? This one will be much clearer from the attached example document; I wish to move two figures (red, strike-through numbers in the "Grand total" column to a position two rows above)

Many thanks for all replies!
Alex

 

[Chihiro]

1. Depends. Discuss with your stakeholders what they want to see in report and how it should be presented. In most cases, I have 2 separate tab for weekly/monthly summary.

2. Calculated fields are meant for value field, which is designed for numeric values and not strings. What you can do is make the function return error, then in display option of the pivottable, specify what string to replace errors with.

3. No. Pivot Table works in context of row label fields.

 

[NARAYANK991]

Hi ,

A pivot table calculated field accepts all of the following :

=IFERROR(Sales /Targets, 0)

=IFERROR(Sales /Targets, NA())

=IFERROR(Sales /Targets, "N/A")

The problem with the last is that the pivot table will not display the string "N/A" ; it will display the #VALUE! error value.

Narayan

 

[NARAYANK991]

Hi ,

The error display can be eliminated by using Conditional Formatting.

Narayan

 

[alexander starbuck]

Thanks everyone!

 

[Chihiro]

To elaborate on my 2nd point. Go to PivotTable Options. On first tab, locate format. There you can control what to show when error value shows up (most often left blank).