pivot tables + calculated fields

[mrexcelis]

Dear all,
i'm new to this forum and hope someone could help me with one quite disturbing problem that i'm having using excel pivot tables.

i have a pivot table with different data. one column is DocsOnTime where the entries are 0 and 1, where 0 is on time and 1 is not on time.

from all these entries i want to get out data in % of how many docs are on time.
if i just put this field in pivot table values field i get the final value in % of data on docs NOT on time..so I need the other way way around.

i tried using -AVERAGE(DocsOnTime)+1 which would give me what i needed but it doesn't work.

i'm having quite some trouble with getting formulas work in calculated field. any clue why am i not able to use =COUNT(DocsOnTime) etc..?
as well tried changing value field settings but without luck..
any advice will be appreciated.

i could attach some pivot to get a more clear picture if needed.

[asar_k]

Hi,

Any sample workbook you could share please? It becomes a lot easier to relate and understand.

[mrexcelis]

here is an example. with lot less data but the same problems.
Book3 .xls

[asar_k]

I am not sure if 0s can be added to the values field. I could be wrong. I propose a workaround for this, where instead of using 1s and 0s for the DocOnTime column, you could use a yes or no.Then, this (DocOnTime)could easily be kept in the Row Labels and again in the values field.

I have attached my version here where the DocOnTime appears once in the Row Label, and twice on the Values field, once for the count and again for the % Total.

Please let me know if anything is unclear.

[mrexcelis]

the 0 field itself when you put it in excel table works fine if you write like =Count() but when in Pivot it somehow doesn't want to work..
well - putting yes and no would be a solution. i have to recreate the query which gives me all this data. will see if it's going to be ok.
thanks in advance. and i will post in case of solution.

[asar_k]

Hmm... I'm still thinking....
Why can't we just interchange the values in the DocOnTime column in the raw data; 1 with 0 and 0 with 1??
That way the Pivot can read the non-zero numbers and give us the correct figure!

Try this once, and let me know if this suggestion helps.

[mrexcelis]

what do you mean exactly?
the bad thing is that my data source is an access DB from which query are these all data coming from.
it's done via this query part:
DocOnTime: IIf(DateDiff("d";[ExpDateOfDeliveryReal];[InvoiceInputDate])<30;0;1)
i cannot change the 30;0;1 else it will give me wrong data as there are other things relying on this.