• 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.

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

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
 
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.
 
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
 
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).

upload_2017-10-31_9-49-18.png
 
Back
Top