• 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.

Grouping of Count Values in a Pivot Table

adityakapoor

New Member
Hello,
I am struggling to manage something with a pivot table (which I could do with multiple columns and COUNTIFS functions.
I am looking at sales data
I would like to understand the frequency of ordering of my entire customer base. So in the pivot table I can calculate the count of orders by customer (using customer in the rows and count of order #) in the sum column.
Now I have a list of all the customers and the number of times they have placed an order.
What I would like to end up with is a list which shows how many customers ordered 1-3 items, how many 4-6 times etc.
So its a count of a count (am I making sense?)

I managed this without pivot tables by using the unique function to get a list of the customers.
Then used a countifs function to calculate the number of orders in a specific period of time by that customer.
Then I used a helper column where an IFS function helped me label the customer as 1 to 3, 4 to 6, 7 to 9 and so on based on the count value
Then I made a column with the brackets listed (i.e. 1 to 3, 4 to 6, etc)
Finally another column to do a quick COUNTIF (for the list of rows with 1 to 3, 4 to 6, ETC)

Problem is that we have no clue how many customers will be there in the list. So I have dragged the formula down to row 500, but as the data range expands, this might not suffice. So surely there is value in using a pivot table. I can easily get a list of the customers and the number of times they have ordered. How do I then group that into number of customers who have ordered 1 to 3 times or 4 to 6 times, etc?
 

Attachments

  • Screenshot 2021-11-27 at 1.11.30 AM.png
    Screenshot 2021-11-27 at 1.11.30 AM.png
    255.4 KB · Views: 7
yes... but when you use numbers in the rows, it lets you do it, when you use customers (i.e. names of customer) in the rows, it won't let me do grouping. I get an error. see the screenshot below:
 

Attachments

  • Screenshot 2021-11-27 at 2.27.24 AM.png
    Screenshot 2021-11-27 at 2.27.24 AM.png
    424.2 KB · Views: 7
I could also consider adding a helper column to the data table where I do a quick lookup of the number of times a customer has ordered using a countifs function, and then in the pivot table group based on this helper column?
would only add a manual step when importing monthly data...
I can post an image if what I say is confusing.
 
adityakapoor
Did You check that my sent snapshot?
Did You notice that, it counts those b-column values?
Here my sample file.
If this won't help You, then You should send a sample Excel-file.
 

Attachments

  • adityakapoor.xlsb
    10.4 KB · Views: 2
Find attached the excel sheet. It has the data on one sheet and the Pivot / calculations on another. I have got the result using formulae but cannot figure out how to use the pivot to calculate the groupings.
 

Attachments

  • Sales Analysis.xlsx
    39.7 KB · Views: 8
adityakapoor
Did You check that my sent snapshot?
Did You notice that, it counts those b-column values?
Here my sample file.
If this won't help You, then You should send a sample Excel-file.
I looked at your file. You are assuming that I start with data which has the count of sales orders for a customer included. Unfortunately I do not have this. I get a dump of all sales data from our invoicing system and calculate the count separately using COUNTIF functions. I am trying to avoid doing this and have the pivot spit this out for me grouped as mentioned above.
Please refer to the file I uploaded in my post.
 
I don't know how you got them to group like that.
did you make the pivot based on the main "Data" Table or did you make it based on the COUNTIF's which I built?
Even when I try to make a pivot based on the COUNTIF's ,I do not see the option to group them like you do. I don't know if this is something specific to a Mac vs windows since we do not have the option to add data to model if that's what you did.
When I choose the first column in the pivot and select group, it comes out ike what I have attached. I do not see the options to group from 1 to 3, etc.

Also, my pivot table shows blanks whilst yours doesn't ... hmmm
 

Attachments

  • Screenshot 2021-11-30 at 10.25.44 PM.jpg
    Screenshot 2021-11-30 at 10.25.44 PM.jpg
    162.4 KB · Views: 7
  • Screenshot 2021-11-30 at 10.25.32 PM.jpg
    Screenshot 2021-11-30 at 10.25.32 PM.jpg
    161 KB · Views: 5
  • Screenshot 2021-11-30 at 10.19.43 PM.jpg
    Screenshot 2021-11-30 at 10.19.43 PM.jpg
    38.3 KB · Views: 2
  • Sales Analysis.xlsx
    49 KB · Views: 1
I deleted all extras from Your sheet - and - I won't use Your formulas or so.
Did You want to group that - Pivot-table which has results?
... then You would group that ... and ... # of Orders ... as below.
You can ungroup it ... and ... group again.
Screenshot 2021-11-30 at 19.23.14.png
Please, try to focus only that Pivot-table .. only that.
 

Attachments

  • Sales Analysis.xlsx
    38.2 KB · Views: 3
I tried to group and ungroup but it still does not show me the option to group starting at 1 etc.
When I set up the pivot the same way as you have done, it puts the # of orders and the list of customers in the same column whilst in your case it shows up as two columns.
Lastly, I have deleted the calculated data since I am still unsure how to get this table from the raw data table. You referred to the raw data table for your pivot or the information in the same sheet? See the sheet without the calculated values attached.
 

Attachments

  • Screenshot 2021-12-01 at 11.22.18 AM.jpg
    Screenshot 2021-12-01 at 11.22.18 AM.jpg
    169.4 KB · Views: 0
  • Sales Analysis.xlsx
    23.6 KB · Views: 3
I do not have many new ideas
... what are You doing there?
Your original Pivot used Your calculated data (table) ... which give useful data.
You should able to see - which data I've used with my Pivot-table (same as You've used ... before Your today's reply).
I tested something based Your raw data ... but I didn't get solution, which gives same results ...
 

Attachments

  • Sales Analysis (1).xlsx
    27.2 KB · Views: 1
My original pivot was based on my raw data. I had no choice but to make the calculated fields to make a pivot to show the data in the grouping I was looking for. Problem is that on the dashboard I cannot use slicers, etc when I make calculated fields, but I can if I can make that sort of a table using a pivot from the raw data. Say for instance if I wanted to see by region how what is the customer ordering frequency. With a pivot I could have a slicer for the regions, but not possible with calculated fields.

So my hope / ideal scenario is if I can get a table which shows the number of customers who bought 1-3, 4-6, 7-9, 10 or more times from the raw data itself but based on a pivot rather than on calculated tables using the UNIQUE and COUNTIF functions.
 
Also, Your calculated column "x" has a hard set date for the year since it refers to two fields for 1st of Jan and 31st of Dec. This is not sortable with a slicer of course.
Additionally, For whatever reason, when I set up the pivot table the same way as you, my fields do not show up in columns but in one column alone. See the attachment.
 

Attachments

  • Screenshot 2021-12-01 at 2.10.31 PM.jpg
    Screenshot 2021-12-01 at 2.10.31 PM.jpg
    389.7 KB · Views: 4
  • Screenshot 2021-12-01 at 2.04.52 PM.jpg
    Screenshot 2021-12-01 at 2.04.52 PM.jpg
    307.1 KB · Views: 2
adityakapoor
Say for instance if I wanted to see by region how what is the customer ordering frequency.
So my
hope / ideal scenario is if I can get a table which shows the number of customers who bought 1-3, 4-6, 7-9, 10 or more times from the raw data itself but based on a pivot
Can You send a layout of ... something
... what do You really would need to get?
... ... even manually done.
... as well as
... ... what would You need to do with it=
I could try to do it without Pivot-table.
Ps
My 'x'-test was a test.
Ps2
Like <> Need
 
I suspect strongly that the slickest solution to this would be with Power Pivot and DAX; not knowing enough about this myself I can't give you a solution. The next possibility would have been by using Power Query; I could do that but I think it would be quite inflexible (when choosing date ranges) so I've gone for sticking with formulae, with or without pivot tables.
In your Pivot sheet, you had formulas extending down the sheet quite a long way, and you had other formulae to cater for varying range extents depending on the size of the Data table on the Data sheet.
You started using dynamic functions in cell B5 but you stopped there. In the attached, I've made many more of your formulae into dynamic ones - they're shaded in light green. This means that your Data table can be any size and the formulae will cope with the entire range without needing alteration.
I've also added a couple of pivot tables based on those calculations, however, in order to get those pivot tables referring to a dynamic range I've added a dynamic Named range named Data2 which the pivot tables use.
So you could have those calculations on a hidden sheet and you never need to see it (except to change the date in cell C5 (but you could make that formula refer to a date in a cell on a visible sheet)).

I realise this isn't straightforward to set up but it should be easy for the user - an update of the Data table would only require refreshing the pivot tables (if you end up using them), otherwise nothing would be required for your data to update.

Just some ideas.
 

Attachments

  • Chandoo47222Sales Analysis.xlsx
    254.1 KB · Views: 3
Thanks everyone for all your help.
I found that by making two pivot tables, it was possible to achieve the grouping that I was looking for. It was not possible with one pivot table. So when I made the first pivot table with just the customer name and the count of orders, then I made a second table linking to this table, and in that table I was able to sort the count of customers into the groups as desired. PHEW!
See attached what my dashboard looks like. I am tempted to make a video to explain the logic behind what I was trying to achieve and what data I started with ending with the pivots and the assembly of the dashboard to help others since I feel this should be a relatively common business requirement for people.
The one thing I wish I could do was figure out how to format the slicers where I can choose the color of a button when a specific item is selected and also change the font... this for whatever reason is not something I am able to do on the Mac ...

Once again, thank you @vletm & @p45cal
 

Attachments

  • Screenshot 2021-12-02 at 2.50.07 AM.jpg
    Screenshot 2021-12-02 at 2.50.07 AM.jpg
    879.3 KB · Views: 8
would also be interesting if anyone knows how to lock the shapes in the dashboard so they don't move around. The minute I try to lock them via protect worksheet, I cannot change any of the slicers... I want the buttons to be usable but the shapes and charts should not be moved around / dragged around by mistake.
Maybe I should make a new post / thread for this query...
 
thank you once again! problem I was running into was that everything was grouped together and if you group everything together then you cannot even see / select the locked option, it was greyed out. I had to ungroup everything, remove the slicers from the group and then regroup and lock all objects leaving the slicers unlocked. Then it works!
 
Thanks everyone for all your help.
I found that by making two pivot tables, it was possible to achieve the grouping that I was looking for. It was not possible with one pivot table. So when I made the first pivot table with just the customer name and the count of orders, then I made a second table linking to this table, and in that table I was able to sort the count of customers into the groups as desired. PHEW!
See attached what my dashboard looks like. I am tempted to make a video to explain the logic behind what I was trying to achieve and what data I started with ending with the pivots and the assembly of the dashboard to help others since I feel this should be a relatively common business requirement for people.
The one thing I wish I could do was figure out how to format the slicers where I can choose the color of a button when a specific item is selected and also change the font... this for whatever reason is not something I am able to do on the Mac ...

Once again, thank you @vletm & @p45cal
I am looking to do something similar with my data....what was the second table that you created based on?
 
Back
Top