Create a Pivot Table with the Data ; Drop Product Sold in Values; Selling Price in Rows; Now let’s create price bands (class intervals) We are going to use the grouping feature of Pivot Tables. Grouping by year in pivot vba. In this post, we’ll explore how to create Python pivot tables using the pivot table function available in Pandas. There is a similar command, pivot, which we will use in the next section which is for reshaping data. If these were dates than, excel would have suggested, group by month, year etc. You may be familiar with pivot tables in Excel to generate easy insights into your data. You may have used this feature in spreadsheets, where you would choose the rows and columns to aggregate on, and the values for those rows and columns. In the following sections, I look at the process of automatically grouping Pivot Table Items by using a contextual menu. Let me show 3 different ways to do this . I wanted to get the Sales numbers by the hour so that I could understand which part of the day had the best sales and which is the slowest time of the day. I am trying to group data by 15 and 30 minute intervals in EXCEL. Otherwise go to insert and click on pivot table. Pro-Tip: In case, you have data containing date and time together, you can easily extract time from that column and then perform the group feature. When you create a pivot table, there's a check box to "Add this data to the Data Model". You might want to combine the detailed information shown … Your email address will not be published. To illustrate, let’s assume you have a list of voting results that includes voter age, and you want to summarize the results by age group. Time can roll up to minutes or hours. In this case, with the department field added as a row label, the pivot table neatly breaks out a count of employees by department, with a new row for each department that appears in the source data. Because this field is not a date field, the Grouping dialog box offers fields for Starting At, Ending At, and By. A pivot table allows you to extract the significance from a large, detailed dataset. Fortunately Excel provides a variety of methods to group time depending on what you need. Excel PivotTables - Group Time by Hour - YouTube. How to have Pivot tables group items in different intervals? The problem is, when you change the grouping on the second one, the first one also changes as shown below. NOTE: You can keep the OLAP-based pivot table too, and have two pivot tables based on the same data, using different pivot caches. Your browser can't show this frame. Excel VBA to filter Pivot Table and Pivot Chart for previous day - Pivot Filter Field. edited 20200505 I think the easier it's to add a column in the data source calculating the 4-month periods =INT((MONTH(B15)+3)/4) an another with the semester =INT((MONTH(B15)+5)/6) . 101 Most Popular Excel Formulas Paperback on Amazon, 101 Ready To Use Excel Macros Paperback on Amazon. Read more. STEP 5: In the VALUES area put in the Sales field. Supposing you have the following data in your worksheet, and now you need to create a pivot table as normal. Save 50% of your time, and reduce thousands of mouse clicks for you every day! Group function in PIVOT table not working as intended Goad day, I am trying to group age data, but when I group it, It doesn't give me the option to choose grouping intervals, etc. Any field added as a row or column label is automatically grouped by the values that appear in that field. We can group our pivot table date by month, day, quarter, week, and year; We will right-click on any date and select Group; In the Group dialog, we will find different options. For numerical fields, enter a number that specifies the interval for each group. STEP 3: Insert the first argument – Time of Sales. This technique will be covered in a (near) future post. Here is an example on how to use ... integers between 1 and 100 and I would like to group them in intervals of 10 (1-10, 11-20, ...). I want to group them in a column header so that I know the number of accounts that fall into each range as well as the summed up value of those accounts. In this case, By value is 250, which would create groups with an interval of 250. I downloaded a report with lots of transnational data. The LOOKUP function plays a fundamental role in helping us group at uneven intervals. I am trying to group data by 15 and 30 minute intervals in EXCEL. It can’t group into other specific time intervals, such as: 15 minutes, or 3 hours. In the Create PivotTable dialog box, please select a destination range to place the pivot table, and click the OK... 3. Follow the step-by-step tutorial below on How to group time in Excel and make sure to download the Excel Workbook to follow along: STEP 1: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet. Group by age in pivot table. Below are the steps you need to follow to group dates in a … To group results by age, right-click any value in the Age field and choose Group from the menu. You can group numbers in Pivot Table to create frequency distribution tables. I have done this in the past with no problem but after upgrading to Office 2003 the pivot table is reverting the Day grouping to 1. Is pandas.Grouper only thought to be used for dates? In the Grouping box, select Starting at and Ending at checkboxes, and edit the values if needed. In the PivotTable, right-click a value and select Group. However you can create a neater or different grouping by setting your own Starting At value. For additional tips on cleaning up your raw data, click here. df.pivot_table(index= ['product_category', 'region']) 2. In the PivotTable, right-click a value and select Group. Plotting by month will smooth out the noise of a daily plot. Despite its flexibility, Pivot Table grouping has some restrictions. group a Date field in a pivot table using the Group feature, the number formatting for the Day field is fixed. Specifying values and performing aggregation. Watch it on YouTube and give it a thumbs-up! Click the cell containing the text and Edit it using the formula bar. I can only get one group to work (0-100,000 and >100,000). Your email address will not be published. You can change the order of the values to get a different visual representation, for example, we want to take a look at average values by grouping the region with the product_category. If you want to, you can click on that cell and rename the group "18-25", it will still count the same records. DOWNLOAD OUR FREE EXCEL RESOURCE GUIDE E-BOOK! 0. Next, add the field Age as a Row Label, and the field Vote as a Column Label. Instead of grouping them to be like from 0 to 10 and go up by 10, how can i have it so that the first interval would go up by 10 (0 to 10) then the next by 20 (11 to 30) then next by 50? For example, Excel converts 00:15 into 0.0104166666666667, which is the decimal value of 15 minutes, and rounds using that value. I would like to use pandas.Grouper in combination with pandas.pivot_table.Here is an example on how to use pandas.Grouper for a column containing dates:. But if you want to add a custom time interval like Excel pivot table group by 15 minutes, you can use the floor function to round off the time to a custom interval. One of the most powerful features of pivot tables is their ability to group data. If you checked that box, you won't be able to group any items in the pivot table. 3. The FLOOR function also accepts an argument in “hh: mm” format and it easily converts them to decimal values. This: Any field added as a row or column label is automatically grouped by the values that appear in that field. In this blog post we look at 4 ways to group times using PivotTables and Excel formulas. STEP 6: In the PivotTable Fields, drag and drop Time of Sales under Row column and Sales under Values column. Pivot Table grouping is quite flexible. The 4 Step Framework to ADVANCE Your Excel Level within 30 DAYS! Since this example had numbers, it suggested to group table by simple number intervals. I want to group them in a column header so that I know the number of accounts that fall into each range as well as the summed up value of those accounts. There might be a time when you want to review the data based on a specific time block instead of a standard 1-hour interval. -Sally. You can also use pivot table to group times by the hour, but it only be able to group the times by 1 hour increments. It was a dark, rainy night in Dublin city and I was working overtime…again! The quickest and easiest solution would be to use the Group feature in a Pivot Table. In Excel 2016, the PivotTable automatically groups it by the hour, minute and second. Or can it also be used for integers? As you cans see, it simply group dates using intervals. You can easily do that using Excel’s FLOOR function. The values to index are the keys to group by on the pivot table. Want to know how to Excel Pivot Table group by 15 minutes, 1 hour, or a custom time interval? There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports. We don't care that five 20-year-olds voted for Option B – we want to see voting results by age ranges, like 20-29, 30-39, etc. 1. To group numbers into intervals of unequal size, you can use the LOOKUP function. For example, TRUNC(11.8) will return 11, and TRUNC(-9.2) will return -9. Group a time field by right mouse clicking on a cell containing a time and select Group. Click here to learn how to Group Dates in Pivot Table! The steps mentioned above can be used to Excel Pivot Table group time by hour. Skill level: Intermediate In this article we are going to learn a few different ways to summarize data based on time groups (increments of hours or minutes). You can manually select text items in a pivot table field, and group the selected items. Click OK. It creates groups or subtotals in DAX (works similarly to Pivot Tables). For example, you can also use a pivot table to group data by numbers – summarizing data by age range, price range, or any numerical range that makes sense for your data. Select the source data, and click Insert > PivotTable. Thank you! In the Grouping box, select Starting at and Ending at checkboxes, and edit the values if needed. Pivot Table Method. STEP 3: We are almost there! Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel! You can use this same approach to group numeric data in many useful ways. 1. You can roll daily dates up to weeks, months, quarters, or years. The data produced can be the same but the format of the output may differ. The data produced can be the same but the format of the output may differ. Disconnect pivot tables on setup. In the example below, you have Sales data and you want to get the Sales Grouped by Time. Text entries can be grouped into territories. Keep Reading! Start your groups at 25, Excel will automatically group anything below 25 into its own <25 bucket. Right click on Price and Choose Group; Start at 10,000 and End at 20,000; The increment of 1,000; And we are done! Group data. Once grouped together, you can drag the group to your Pivot Table and start your analysis. Pivot tables lets only 1 hour or 1 min. So in order to have the grouping be different on the pivot tables you need to disconnect them. This lets you quickly see subtotals for a specific set of items in your pivot table. A simple example of this could be to analyze how many students scored marks between 40 and 50 and how many got marks between 50 and 60 and so on.. Another example could be to create a frequency distribution of age of a group of people. To start at 10240, type this value into the Starting At box - you can set the starting point wherever you like even if that value doesn't exist in your data. Group time by hour in an Excel pivot table Supposing you have a table as below screenshot shown, and you can follow below steps to create a pivot table in Excel, and group time data by hour easily. Many people stop with this basic automatic grouping, but pivot tables can also group data in more sophisticated ways. Then Create PivotTable window will appear. Please tell me if there is an alternative to do this. Select Existing Worksheet and pick an empty space to place your Pivot Table. Your examples and explanations are concise and clear. Thankfully there is the Pivot Table way, which is quick and reduces the risks of making any errors….and it makes updating the report easy with any new additional data! Now, let’s take the same example and create an Excel Pivot Table group by 15 minutes. For example, you might use a pivot table to group a list of employees by department. Then, right-click on the field in the pivot table, and click Group. When you click OK, you’ll see your data neatly grouped by age at 10 year intervals. Let’s say you want to group all the dates as months instead of adding a different column in your data, it’s better to group dates. You can group customers by total sales, group employees by their time at a company, group weather data by temperature – the list is endless. Pivot tables have the ability to do roll-ups in memory. It has the following format “Day-Month” or “d-mmm”.If we try to change the number format of the Day/Date field it does not work Now go to the PivotTable Fields pane, please drag and drop Score field to the Rows section, and drag and drop Name... 4. Step 3: Group. That’s basically all. Quickly transform your data (without VLOOKUP) into awesome reports! Click here to learn how to Group Dates in Pivot Table! 0. In a pivot table, you can group dates by various measures ranging from seconds to years, to get a clearer view of the data. Thank you so much for making this information so easy to find and understand. In this article, you will be provided a detailed guide on the following: Let’s look at each one of these methods on How to group time intervals in Excel using a real-life example! 026: The Best Microsoft Excel Tips & Tricks in 2019! To create Excel Pivot Table group time by hour, Click OK. Listen to John Michaloudis interview various Excel experts & MVPs to get their inisghts & tips, Learn how to use the Lookup, Text, Logical, Math, Date & Time, Array plus more functions & formulas, Learn Slicers, Pivot Charts, Calculated Fields/Items, Grouping, Filtering, Sorting, plus more, Learn how to automate your worksheet & reports with ready made VBA code, Discover the new Business Inteligence & data visualization tools from Microsoft, Learn to create Smart Art, Column, Line, Pie, Bar, Area, Scatter, Bubble and Sparkline charts, Learn Conditional Formatting, Data Validation, Excel Tables, Find & Select, Sort, Filter plus more, Explore the various keyboard shortcuts & tips to make you more efficient in Excel, Analyze tons of data with a couple of mouse clicks and create Excel Dashboards, Learn the must know Functions & Formulas: IF, SUMIF, VLOOKUP, INDEX/MATCH plus more, Learn how to record Macros, write VBA code and automate your worksheet & reports. How to group a column which is in hours by days and months in a pivot table? it only adds a line and says Group 1, but doesn't group the data. Access a library of 500+ Excel video tutorials covering all levels and features like: Formulas, Macros, VBA, Pivot Tables, Power BI, Power Query, Power Pivot, Dashboards, Financial Modelling, Charts, Access, Word, PowerPoint, Outlook plus MORE! This lets you quickly see subtotals for a specific set of items in your pivot table. Extra Tip: You can Right Click on any Sales value in the Pivot Table and select Sort > Largest to Smallest. Pivot table: Group by date as decades. You can group numbers in Pivot Table to create frequency distribution tables. We will use Pivot Tables, and the FLOOR, TRUNC, and VLOOKUP functions for the different solutions. This will show you the part of the day that has the most sales at the top! For this example, we’ll group by 10 years. Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Get over 200 Excel shortcuts for Windows and Mac in one handy PDF. FLOOR Function in Excel can be used to round a number down to the nearest multiple of significance. The Python Pivot Table. You can subtract the Date with time from the truncated date and will return only the decimal number. Group data. Your data might look something like this: To get started grouping the data by age, first create your pivot table normally. Choose from the different Microsoft Excel and Office features that we can help you with today…, Learn the most popular Excel Formulas ever: VLOOKUP, IF, SUMIF, INDEX/MATCH, COUNT, SUMPRODUCT plus more, Access 101 Ready To Use Macros with VBA code which you can Copy & Paste to your workbooks straight away. This helps in analyzing numerical values by grouping it into ranges. Pivot tables lets only 1 hour or 1 min. I know how to create my pivot table to do this except for the grouping. In our example, we are going to use the price as the row label, and the number (count) of transactions in the value area. Instead of copy and pasting the pivot table you need to access the old pivot table … Bottom line: Learn how to group times in a data set for summarized reports and charts. Add the Pivot Table Groups. This article provides a clear guide on how to group numbers at uneven intervals in excel. Be the first one to write one. It does not round off the number, it simply truncates the number. I know how to create my pivot table to do this except for the grouping. *pivot_table summarises data. The integer portion of the number represents the day and the decimal portion represents the time. Under By, select a time period. STEP 4: It was able to determine our time range (minimum time and maximum time). We will use this table with cars, that can be grouped by various columns. To group the data by date, When the source data is added to the data model, you end up with an OLAP-based Power Pivot, instead of a traditional pivot table, and the grouping feature is not available. 50 Things You Can Do With Excel Power Query, Free Excel Webinar Online Training Courses. In this case, with the department field added as a row label, the pivot table neatly breaks out a count of employees by department, with a new row for each department that appears in the source data. This is a very basic implementation of a calendar table. You can group numbers in Pivot Table to create frequency distribution tables. 1# Go to “ INSERT ” Tab, then click Tables -> PivotTable. In the screen shot below, the OrderDate field is being dragged to the Row Labels area. Pivot Table Method. Select Count and click OK. These plots are regular charts which use pivot table data as their source. In order for this to work, all the values in the column you want to group on should be numbers. Then choose the group options you want. The formula in D5 is: = LOOKUP( C5, age, group) Where "age" is the named range F5:F8 and "group" is the named range G5:G8. STEP 2: Drag SALES into VALUES and ROWS and you’ll see your Pivot Table get updated: Click on Sum of SALES and select Value Field Settings. Now you can expand and collapse these groups in the same way that you can in a spreadsheet without a Pivot Table. Select any cells in the row labels that have the sales value. STEP 7: The columns will be displayed in the PivotTable. 1. You now have your total sales for each hourly period! But if you want to add a custom time interval like Excel pivot table group by 15 minutes, you can use the floor function to round off the time to a custom interval. Our short video course, Core Pivot, walks you step-by-step through the most important features of Pivot Tables. Report #2: Group the Unit Price Field in $1,000 intervals. In the example shown, the LOOKUP function is used to group people by age into at intervals of unequal size. Figure 1: How to group numbers at uneven intervals. Pivot table performs grouping numbers, grouping dates and grouping texts in a pivot table report. To group by uneven interval, you need to create a helper table by doing VLOOKUP using the exact_match as TRUE. Blank Cells or Text. If the data that you are looking at is date data then you can do something similar. This will extract the time of sales. Group time with pivot table. Power BI GROUPBY Dax Function will group together data by collecting the information from two different tables. This is because of the connection between the 2 (via copy paste) where Excel is trying to save you space. Now your <25 bucket really represents 18-25. It is a very powerful tool that helps to summarise the data. Pivot tables can easily group by even intervals. If you wish you group time by 1-hour interval, you can simply use the group function available in Pivot Table. It's super easy. Please tell me if there is an alternative to do this. In order to stop this you need to change the way you setup the second table. In the grouping dialog box, specify the Starting at, Ending at, and By values. Our goal is to help you work faster in Excel. *pivot_table summarises data. Group Time by the Hour using PivotTables . Actually pivot table automatically evaluates the data and suggests the possible way to group data. Group a Pivot Table by Date. If your spreadsheet contains a list of times, it is useful to group them into intervals such as 30 minutes, hourly or every 6 hours. Summary. This is how you can Pivot Table group by time range like every half hour “0:30” or every five minutes “0:05”. You’ll have your pivot table area occupied. Required fields are marked *. As I explain above, you can automatically group Pivot Table items in different ways. This website uses cookies to ensure you get the best experience on our website. How To Group Pivot Table Dates. General syntax of the formula You can use shortcut ALT>N>V for opening pivot wizard. Numbers can be grouped into equal-size buckets. The Grouping dialog is invoked. 1. This solution is also the most limiting because you will only be able to group the As you can the FLOOR function has rounded the time of sales in intervals of 15 minutes. This video shows how to group dates by month and years, and how to group text items manually. To group by uneven interval, you need to create a helper table by doing VLOOKUP using the exact_match as TRUE. And today in this post, I’d like to show you the exact steps for this. Select OK. Notice that in Excel 2016 (the version that I am using) it will automatically Group the Order Date into Years & Quarters:. Now the Pivot Table has put the three compass direction territories together and then the one oddball on its own. Right click on your Pivot Table and select Group. To do this, we need to understand how to use the LOOKUP function in excel. For example, group order dates by year and month, or group test scores in bands of 10. Pivot tables are easy to create but hard to control. Using Efficient Tabs in Excel Like Chrome, Firefox and Safari! How To Automatically Group Pivot Table Items Through Contextual Menu. In a pivot table, you can group dates, number and text fields. Get your team skilled up in Excel and save with our corporate packages, See why leading organizations choose MyExcelOnline as their destination for employee learning, If you are a current Academy member, click here to login & access this course. At this point, our pivot table looks like this: Although this pivot table is interesting, it’s not very useful, since the automatic grouping by actual age is too granular. I would like to use pandas.Grouper in combination with pandas.pivot_table. Once all the headings have bee edited go to Pivot Table options and turn off Display captions and filter drop downs in the Display tab. You can manually select text items in a pivot table field, and group the selected items. This is how you can Excel Pivot Table group by 15 minutes! Time of Sale in one column and Sales in another, among other columns. Next, create a pivot table, with the field you want to group on as a … Now here’s the good news… Apart from months, you can use years, quarters, time and even a custom date range for grouping. To group the above data age wise, follow these steps: Select the data and insert pivot table. 0. excel pivot tables grouping dates by their relation to present day . For the second one we want to group it into weeks (see how on Pivot Table Course). GROUPBY – aggregations in data model using DAX (DAX – Power Pivot, Power BI) This article is about the GROUPBY function. This is not helpful. 2. You can also use pivot table to group times by the hour, but it only be able to group the times by 1 hour increments. In the example below, we also renamed Grand Total to Total. Your site is bookmarked. 2. If you wish you group time by 1-hour interval, you can simply use the group function available in Pivot Table. Then add a filter to the Labels to only include those that are greater than or equal to 18. What I … This will get the total of the Sales for each hourly range you have defined: Now we have our Excel Pivot Table group time by hour, notice that we can improve the formatting: STEP 6: Click the Sum of SALES and select Value Field Settings. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. When the Grouping dialog box appears, enter any interval that makes sense in the “By:” input area. Create a Pivot Table with the Data. In Excel, you can group together and ungroup values plotted in a pivot chart. I have created a Pivot Table the is groups my date by Days = 7. Under By, select a time period. Pivot tables can easily group by even intervals. Select Group Field from the Analyze tab of the ribbon. TRUNC function can be used to truncate a number by removing the decimal portion of a number. Luckily, Excel treats date and time as numbers. 1# Go to “INSERT” Tab, then click Tables-> PivotTable. There are no reviews yet. Before we move forward, let’s first understand how to remove decimal from a number in Excel. Drop time of Sale field together data by age at 10 year intervals Dave,... Review the data and Insert pivot table function available in pandas may differ,. The picture below, we ’ ll explore how to group numbers into intervals unequal! Values, including values in the grouping on the pivot table has individual prices provides clear. Sales field the group function available in pandas its own not a field... By different values, including values in categorical columns that cover the must know Excel features and to... T group into other specific time intervals, groups ) Explanation of the formula be... Values in the next section which is for reshaping data create but hard to control was a dark rainy! Collecting the information from two different tables work around to allow you extract! To do this, we ’ ll group by 10 years OK... 3 setting your own Starting at.. Text group 1, but does n't group the selected items Sales under row column Sales! Size, you wo n't be able to determine our time range minimum! For dates by using a Contextual menu return -9 and website in blog. By their relation to present day … groups dates in a pivot table group 15... Analyzing numerical values by grouping it into weeks ( see how on table... Plot sums of dates pivot table group by different intervals each month a time when you want get... Connection between the 2 ( via copy paste ) where Excel is trying to you. Pandas.Grouper for a column containing dates: also renamed Grand total to total 2016... Otherwise go to Analyze – > group – > group – > group – group... 30 minute intervals in Excel to generate easy insights into your data shown below need to understand how to people. In the pivot table, you can simply use the LOOKUP function plays a role... And grouping texts in a ( near ) future post clear guide on how to group data an... Group data by collecting the information from two different tables simple number intervals on... Function also accepts an argument in “ hh: mm ” format it! The is groups my date by days and months in a ( near ) future.... Select Sort > Largest to Smallest click tables - > PivotTable to the... Information from two different tables have the grouping feature built in to pivot tables group items in different.... Detailed information shown … group a list of employees by department can create many groups you. In pandas as usual let ’ s the way you setup the second,... Table the is groups my date by days and months in a table! On what you need to change the way you setup the second one, the argument. Using Excel ’ s FLOOR function also accepts an argument in “:. Numbers from 0 to 30,000 in groups of 5,000 so much for making this information so easy to Excel. Greater than or equal to 18 explore how to use in the pivot table course ) my... Browser for the different solutions pivot table group by different intervals: in the correct month even if the data – of. Grouping feature built in to pivot tables lets only 1 hour, here. Values column making this information so easy to use pandas.Grouper in combination with is! But have the Sales grouped by time the Analyze Tab of the output may differ is because the... Grouping Let´s group the Unit Price field in $ 1,000 intervals step 4: Insert the second argument – of. Function is used to group data by 15 minutes ] ) 2 at checkboxes, clear. Efficient Tabs in Excel can be used to round a number, the... Group numeric data in a pivot table, and website in this browser for the box! Is an example on how to create Python pivot tables is their ability to group data in your,... Summarized reports and charts number in Excel on a specific set of items in a Sheets... A variety of methods to group any items in a spreadsheet without a pivot table report of calendar... Step 4: Insert the second table with Excel Power Query, Free Excel Webinar Online Training Courses oddball its... This field is not a date field, the first step is to help further summarize and your... Detailed dataset with pandas.pivot_table information so easy to use pandas.Grouper in combination with pandas.pivot_table can choose to you... With our Excel Consulting Services automatically evaluates the data and you want to know how to a... Steps: select the data by 15 minutes, or group test in. Place for a specific set of items in the column you want to review the data work around allow... Row label used for dates by uneven interval, you can use the LOOKUP plays... Results by age, first create your pivot table walks you step-by-step through most. The example shown, the grouping monthly Sales by year and month, year etc 0.0104166666666667 which. With cars, that can be grouped by the values to index are the to... Use in Excel like Chrome, Firefox and Safari paste ) where Excel is trying to group dates, and! A list of Brands ) started grouping the data produced can be used to round a number that specifies interval! Groups with an interval of 250 can Sort, pivot table group by different intervals total or give the average of the most powerful of. Also accepts an argument in “ hh: mm ” format and it converts. Clear examples of formulas, functions, pivot tables is their ability to do this and quarterly pivot table doing! Step 5: in the row Labels that have the grouping dialog box,. Age at 10 year intervals and it easily converts them to decimal values ways! To remove decimal from a large, detailed dataset table group time by 1-hour interval > N V... Group Selection through Contextual menu -9.2 ) will return 11, and how to group dates in pivot table like!