Pivot Table with blank data

[Dolphinv4]

Hi,

I have a pivot table with the dates rows field and a/c no. on the column
fields. however, there are some dates whereby there is no amount because
these are weekends. What I want is for those blanks, I want it to follow the
same amount as the last balance, ie, for saturday & sunday, the balance
should show the balance as at firday instead of NIL.

What is the easiest way to do it?

Thanks.

 

[Roger Govier]

Hi
Double click your Value field>Options>Show data as>Running Total in>Date
If you want to show the actual daily values as well, drag the Value field to
the Data area twice, once set as above and once as Normal.
If you want the columns side by side drag the Data button and drop on Total.

 

[Dolphinv4]

Hi,

tried what you said but it isn't what i wanted. The result is that it is
accumulative. Let me detail my prob:
XXXX YYYY ZZZZ
1/1/07 100
2/1/07 200 300 400
3/1/07 300 300 400
4/1/07 300 300 400
5/1/07 700 400 450

Correct?

Thanks.

Dolphin

assuming 3rd & 4th are saturday & sunday respectively, I'd like the pivot
table to show:

XXXX YYYY ZZZZ
1/1/07 100
2/1/07 200 300 400
3/1/07 300
4/1/07
5/1/07 400 100 50

The above is what I wanted.

However, according to what you said, if I had done it correctly, it appears
this way:

XXXX YYYY ZZZZ
1/1/07 100
2/1/07 200 300 400
3/1/07 200 300 400
4/1/07 200 300 400
5/1/07 400 100 50

 

[Roger Govier]

Hi

If your source data is already cumulative, then the Pt cannot do what you
require.
You would need to create daily figures from your source table.

Assuming your data looks like this
Date Amount Code
1/1/7 100 XXX
2/1/7 200 XXX
3/1/7 300 XXX

and so on with all the data for each of the codes YYY and ZZZ.
First Sort your data by Code and Date
in Cell D2 enter the following
=IF(C2<>C1,B2,IF(B2=D1,0,B2-B1))
Copy down as far required
Put a Heading of Amount2 in cell D1
Extend the range for the source data for your Pivot table to include column
D

Remove Amount from the Data area and insert Amount2 instead.
Refresh the table and you will have the layout you want.

 

[Dolphinv4]

Hi,

no my raw data is not cumulative...my actual raw data is as below.

1/1/07 a/c1 AUD 100
2/1/07 a/c1 AUD 200
3/1/07 a/c1 AUD 300
4/1/07 a/c1 AUD 200
5/1/07 a/c1 AUD 400
1/1/07 a/c2 AUD 100
2/1/07 a/c2 AUD 200
3/1/07 a/c2 AUD 300
5/1/07 a/c2 AUD 400(note: if there's not amount at all on that day
(ie, Saturday 4/1/07), there is no data at all)
1/1/07 a/c1 USD 100
2/1/07 a/c1 USD 200
3/1/07 a/c1 USD 50(note: if there's not amount at all on that day
(ie, Saturday 4/1/07 & Sunday 5/1/07), there is no data at all)

From the above raw data, if i just use the wizard to craete a pivot table,
it'll show the below with all the empty cells. And if i create a graph fr
this pivot table, there'll be empty break lines in the graph:
AUD USD
a/c1 a/c2 a/c3
1/1/07 100 100 100
2/1/07 200 200 200
3/1/07 300 300 50
4/1/07 200
5/1/07 400 400


What I want to show is:
AUD USD
a/c1 a/c2 a/c3
1/1/07 100 100 100
2/1/07 200 200 200
3/1/07 300 300 50
4/1/07 200 300 50 (a/c2 & a/c3 which was previously empty shld
have the same value as the previous day)
5/1/07 400 400 50 (a/c 3 which was previously empty shld have the
same value as the previous day)


HOWEVER, IF I use the method you taught me in your first reply, whereby I
double click on the Value field (data field rite?)>Options>Show data
as>Running Total in>Date, what I get is the cumulative effect (ie, increasing
amounts as the day increases). Have I done something wrongly? Should it be
this way?

Thanks.

 

[Roger Govier]

Hi

The Running Total works exactly the way you describe.
The version using just the actual data will have blanks where are rows with
dates, but not data values.
You can get around the problem for your charts, by having NA() in the cells
rather than blank.

Right click on the PT>Table Options>for Blank cells use NA()