Problems with pivot chart and weeknums

[percy83]

Hi all,

I am having some problems with my pivot chart.

Data setup as follows:

Opening date projectname =Weeknum(A1;2)

I then plot these in a chart but the problem is that some weeks I don't have any sales so the chart is not properly displayed(i.e. not in chornological order 1,2,3,4 etc.) It only plots the weeks that has data.

Like; 1,5,6,8,10.

How to solve this?
I know that you can grop date in clusters of 7 (i.e weeks) but then my chart won't plot weeknums.

This is really driving me mental and all advise is highly appreciated.

BR
Percy

 

[theta]

If I were you, I would use the pivot chart to summarise the data...then (on another tab) build a summary that goes from 1 - 52 (weeks).

In each cell use a =GETPIVOTDATA formula to pull the data from the pivot summary

If using 2007, can wrap it in =IFERROR(formula,"") or using 2003 do the standard =IF(ISERROR(formula),"",formula)

Benefits - you can create a custom report, in the format you want, append formulas and analysis to it easily, but without sacrificing performance (as you may do with SUMIF or SUMPRODUCT) as the pivot table does all the calculation for you

 

[theta]

If not, I think the only other method is to insert dummy rows for the missing weeks, but leave the data empty.

Then it will be used in a pivot without having any impact on the results.

 

[percy83]

Thanks so much for your help!

 

[percy83]

On second thought I am not really sure on how you mean.

I have the weeknums in pivot with a count of project name.

Can I use =GETPIVOTDATA in combination with a lookupfunction to use in the new "week summary" table or do I need to do this manually?

Thanks again!

 

[theta]

Create a summary sheet with the weeks :

1
2
3
4
5
.
.
.

Then in the column next to it select cell B1 type = then select the count cell from within the pivot table that corresponds to week 1.

Excel will generate a GETPIVOTDATA formula!

Where it says "1", change this to A1 (on the current sheet). Then drag it down for all 52 weeks

You will see errors where there is no matching week in the pivot table, so wrapping it in IFERROR will ensure errors are displayed as blank ("") or zero (0).

 

[theta]

Did this solve all of your issues percy?

If not could upload an example. It is by far the fastest, most efficient way of producing summaries (without using VBA) as the pivot table uses SQL to summarise results.

 

[percy83]

Did this solve all of your issues percy?

If not could upload an example. It is by far the fastest, most efficient way of producing summaries (without using VBA) as the pivot table uses SQL to summarise results.

Yes, no probs.

Thanks so much for your assistance!