Question description:
This user has given permission to use the problem statement for this
blog.
i have a pivot table and it is showing 0 for amount values
Solved by S. H. in 45 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
03/09/2018 - 09:43
Hello, Welcome to Got it Pro.
User
03/09/2018 - 09:44
hi
Excelchat Expert
03/09/2018 - 09:44
Can you please forward the sheet so that I can have a look and help you.
User
03/09/2018 - 09:45
ok hang on - give me a minute
Excelchat Expert
03/09/2018 - 09:45
Sure
User
03/09/2018 - 09:48
:-)
User
03/09/2018 - 09:48
i am just masking some data - pl bear with me.i didnt think that i was going to send the file across
Excelchat Expert
03/09/2018 - 09:48
Ok.
Excelchat Expert
03/09/2018 - 09:48
Because I need to have a look on where the problem is
User
03/09/2018 - 09:53
sure - just closing the file
Excelchat Expert
03/09/2018 - 09:55
ok
User
03/09/2018 - 09:57
ok sharing
User
03/09/2018 - 09:58
file shared
[Uploaded an Excel file]
Excelchat Expert
03/09/2018 - 10:00
O my goodness, you have shared 90000 rows of data :)
Excelchat Expert
03/09/2018 - 10:00
where is the pivot?
User
03/09/2018 - 10:01
it was in the other file !!
User
03/09/2018 - 10:01
hang on
Excelchat Expert
03/09/2018 - 10:01
where are you seeking my help
Excelchat Expert
03/09/2018 - 10:01
?
User
03/09/2018 - 10:02
sending u the pivot
[Uploaded an Excel file]
Excelchat Expert
03/09/2018 - 10:02
what was the other sheet about?
User
03/09/2018 - 10:03
it has the pivot which was the problem
Excelchat Expert
03/09/2018 - 10:03
so I can ignore the last sheet that you have shared?
User
03/09/2018 - 10:03
yes please
Excelchat Expert
03/09/2018 - 10:04
where is the base data from where this pivot has been created?
User
03/09/2018 - 10:04
i was analyzing the file building the pivot and started getting zeros... the first file is the base data
Excelchat Expert
03/09/2018 - 10:05
ok so one file has data and other has pivot
User
03/09/2018 - 10:06
yes i had the pivot in the unedited file - i sent u only the base data file after masking some of the entries
Excelchat Expert
03/09/2018 - 10:09
the sheets that you sent me doesn't have the same data from which pivot has been created
Excelchat Expert
03/09/2018 - 10:09
month column is not there plus there are values that are missing
Excelchat Expert
03/09/2018 - 10:09
Still
Excelchat Expert
03/09/2018 - 10:09
Look at the B19 in your pivot
User
03/09/2018 - 10:09
there was no month column - only date and the pivot made the month automatically
Excelchat Expert
03/09/2018 - 10:10
where you created the month?
User
03/09/2018 - 10:10
nope only the DOC_DATE field in the spreadsheet
User
03/09/2018 - 10:11
when i select the field Excel is making the month and Quarter
User
03/09/2018 - 10:11
i have not created this
User
03/09/2018 - 10:12
B19 is showing 0 like all the other cells but they have amounts !!
Excelchat Expert
03/09/2018 - 10:12
I can see the month provided in base data from where Pivot is created but this column is not there in the spreadsheet that you sent me
[Uploaded an Excel file]
Excelchat Expert
03/09/2018 - 10:13
Lets talk about your 0 values for now.. because I can track some of that data.
Excelchat Expert
03/09/2018 - 10:13
Look at B19
User
03/09/2018 - 10:13
ok yup i am at B19
Excelchat Expert
03/09/2018 - 10:13
Can you see the sheet at the right side of this screen?
Excelchat Expert
03/09/2018 - 10:13
I pasted few values
User
03/09/2018 - 10:13
yes
Excelchat Expert
03/09/2018 - 10:14
So B 19 is the sum of these values hence it is 0
User
03/09/2018 - 10:14
these are the values which should show up in the pivot
Excelchat Expert
03/09/2018 - 10:14
306750 and -306750 =0
User
03/09/2018 - 10:14
i got it
Excelchat Expert
03/09/2018 - 10:14
Did it solve your query?
User
03/09/2018 - 10:15
but then i need them both in the pivot
Excelchat Expert
03/09/2018 - 10:15
ohk so you do not want to sum
User
03/09/2018 - 10:15
no s
User
03/09/2018 - 10:15
see each entry has its own story
User
03/09/2018 - 10:15
the - minus means money given out
User
03/09/2018 - 10:16
and the + means money received
Excelchat Expert
03/09/2018 - 10:16
ok
User
03/09/2018 - 10:16
it is an accounts voucher - so it will show Debit and Credit undee the same voucher number
Excelchat Expert
03/09/2018 - 10:16
wait.
Excelchat Expert
03/09/2018 - 10:17
Let me explain you how pivot works.
Excelchat Expert
03/09/2018 - 10:17
for the same entries it will perform calculations like sum, count, average and so on.
Excelchat Expert
03/09/2018 - 10:18
so if you look at the data at the right side of the screen that is associated with B 19 of your pivot
User
03/09/2018 - 10:18
yes ..
Excelchat Expert
03/09/2018 - 10:18
so pivot an show the sum of these two transaction amount which is 0, it can count and say 2 and so on
User
03/09/2018 - 10:19
i got that - but then is there a way to show only the negative amounts ?
Excelchat Expert
03/09/2018 - 10:19
but you just want to populate the data as it is without performing any calculation. you have to move the data from values to column
Excelchat Expert
03/09/2018 - 10:20
populating data only negative or positive is possible but populating data means it should not be in the values field (where pivot perform the calculations)
User
03/09/2018 - 10:20
:-)
User
03/09/2018 - 10:20
it says i cannot place someting with more than 16k rows into column
Excelchat Expert
03/09/2018 - 10:20
but again there is a column limit in excel so pivot can not populate the data for the number of rows you are trying to handle
Excelchat Expert
03/09/2018 - 10:21
powerpivot may help you for this
User
03/09/2018 - 10:21
whats that !
Excelchat Expert
03/09/2018 - 10:21
that is an etc add-in you need to download and install .. that just increase the capabilities and features
User
03/09/2018 - 10:21
i learned pivot over the past 5 days and not i hve power pivot to see <OMG>
Excelchat Expert
03/09/2018 - 10:22
that is not a normal inbuilt in excel. Power pivot is a step ahead of pivot
User
03/09/2018 - 10:22
ok
Excelchat Expert
03/09/2018 - 10:22
Excel can not handle the data with pivot that you are trying with
User
03/09/2018 - 10:22
oh ok
Excelchat Expert
03/09/2018 - 10:22
if you want to perform calculations like sum , we can do it
Excelchat Expert
03/09/2018 - 10:22
Did it solve your query?
Excelchat Expert
03/09/2018 - 10:23
I tried my best to explain you.
User
03/09/2018 - 10:23
yup thanks this has been a great help and yes my query is solved
Excelchat Expert
03/09/2018 - 10:23
Please end the session, I will appreciate a 5 star rating. I will also provide a 5 star rating for you so that you get the best expert next time.
Excelchat Expert
03/09/2018 - 10:23
Thanks for using Got it Pro. Looking forward to help you further.
Appreciate it :)
User
03/09/2018 - 10:23
one last question
Excelchat Expert
03/09/2018 - 10:23
Yeah say
User
03/09/2018 - 10:23
is there a formula which i can use to eliminate the duplicate rows
Excelchat Expert
03/09/2018 - 10:24
Yeah
Excelchat Expert
03/09/2018 - 10:24
you just want to highlight or want to remove duplicates?
User
03/09/2018 - 10:24
sorry ... i meant the second row with hvg the same DOC-REF but a positive value
User
03/09/2018 - 10:25
if i remove the second row my problem i solved - i am interested in the - minus value because that is the pay out
Excelchat Expert
03/09/2018 - 10:25
what do you mean by second row?
Excelchat Expert
03/09/2018 - 10:26
ok you are referring to the number i posted at the right
User
03/09/2018 - 10:26
yup
Excelchat Expert
03/09/2018 - 10:26
this is just the data that pivot has picked up from the base sheet.
Excelchat Expert
03/09/2018 - 10:26
in just one row it is looking easy but for many entries like you have 90000 plus rows it wont be easy to identify
Excelchat Expert
03/09/2018 - 10:27
you can filter the transaction amount (only negative values) manually in base data and copy that in a new sheet
User
03/09/2018 - 10:27
yes i know - but if i run a script or something in the base document i can eliminate the second row - identified by the DOC-REF and then whether the amount is - or +
Excelchat Expert
03/09/2018 - 10:27
after copying the whole data for negative values, create a new pivot,... would be easy
User
03/09/2018 - 10:28
ok filter and then move ahead
User
03/09/2018 - 10:28
fine - thanks
Excelchat Expert
03/09/2018 - 10:28
Thanks :)
User
03/09/2018 - 10:28
this has been a great help and sets my mind to rest :-)
Excelchat Expert
03/09/2018 - 10:28
I hope you will get the solution
Excelchat Expert
03/09/2018 - 10:28
You can end the session, don't forget to rate a 5 star.
User
03/09/2018 - 10:28
i was really worried as i have a delivery in the nxt fewhours
Excelchat Expert
03/09/2018 - 10:28
you will be fine. Have a good day
User
03/09/2018 - 10:28
oh sure 5 start or more
User
03/09/2018 - 10:28
:-)
Excelchat Expert
03/09/2018 - 10:28
:)
User
03/09/2018 - 10:29
tks u hv a good day too
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
