• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

pivot table - custom grouping display count non existing data

P

prasaddn

Active Member
hi, I have two requirements from pivot table.


Requirement 1:

In raw data I have column which has status of transactions like complete, untouched & Hold.

It is easy to show count of transactions based on status.

My requirement is, if on given day even though nothing is on "hold", I still want pivot table to show Hold heading and zero count. is it possible?


Requirement 2:

In raw data i have one column with aging details of transactions. I need to group by aging. the system only gives me the options of grouping setting starting at, ending at and incrementing by what range.

My requirement is, custom grouping like 0 - 5, 6 -15, 16 - 21 and 21 - 40 and so on. Again, even if I dont have any transactions in group 21 - 40, it should show column 21 - 40 and count as zero. Is it possible?


I am currently using table designed to show with my requirements, and using getpivotdata function to fetch data if exists else 0.


Any better solutions?


Regards,

Prasad
 
One more point, I have even tried "Show Items with No Data" option under field settings, but not sufficient enough.
 
here is a simple but not elaborate way to do it.


create your first pivot table. So it'll show the count of complete, untouched and hold.


here is the not so elaborate way to show...

copy your pivot table on to another worksheet as value (so you are creating another data set so to speak). Delete the number under 'Hold'.


Create a second pivot table and change the count to sum. Then you will have a "pivot table" where the hold is blank.


But I'd rather suggest you go back to the original data sheet, think and may be add a status to differentiate those machines that are actually "on hold" vs. those that have nothing going on. Should they be reclassified as "untouched"? I don't know your definition of "untouch" but to me, it sounds nothing is going on with the machines on face value.
 
Hi Fred,


Appreciate your suggestion, but I am not able to see the expected result by following your steps.


Also, how do I customise grouping of aging?


Regards,

Prasad
 
pertaining to your question #1


Basically, i am imagining you already have the first pivot table created where status is on row label. copy the entire pivot table and paste it as value on another worksheet. then from the new worksheet you delete the count on 'untouched' or change it to 0. Create another pivot table in the same format afterwards.


by the way, try not to use 'count' and use 'sum' instead on your #2 and see if it works.
 
You must log in or register to reply here.
Back
Top