|
Pivot Table with blank data
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. |
Pivot Table with blank data
Hi
Double click your Value fieldOptionsShow data asRunning Total inDate 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. -- Regards Roger Govier "Dolphinv4" wrote in message ... 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. |
Pivot Table with blank data
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" wrote: Hi Double click your Value fieldOptionsShow data asRunning Total inDate 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. -- Regards Roger Govier "Dolphinv4" wrote in message ... 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. |
Pivot Table with blank data
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. -- Regards Roger Govier "Dolphinv4" wrote in message ... 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" wrote: Hi Double click your Value fieldOptionsShow data asRunning Total inDate 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. -- Regards Roger Govier "Dolphinv4" wrote in message ... 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. |
Pivot Table with blank data
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?)OptionsShow data asRunning Total inDate, 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" wrote: 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. -- Regards Roger Govier "Dolphinv4" wrote in message ... 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" wrote: Hi Double click your Value fieldOptionsShow data asRunning Total inDate 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. -- Regards Roger Govier "Dolphinv4" wrote in message ... 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. |
Pivot Table with blank data
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 PTTable Optionsfor Blank cells use NA() -- Regards Roger Govier "Dolphinv4" wrote in message ... 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?)OptionsShow data asRunning Total inDate, 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" wrote: 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. -- Regards Roger Govier "Dolphinv4" wrote in message ... 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" wrote: Hi Double click your Value fieldOptionsShow data asRunning Total inDate 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. -- Regards Roger Govier "Dolphinv4" wrote in message ... 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. |
| All times are GMT +1. The time now is 08:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com