Re: [libreoffice-users] Pivot Tables - Monthly subtotals
Howdy, Excellent! Turns out I was so intent on trying to force that row to column header trick I gave you a bogus answer anyway. Glad you found a solution. Best wishes Drew On Thu, May 20, 2021 at 11:19 AM Julian So wrote: > > Actually, I figured out how to get what I wanted. This is the way: > > 1. Build the pivot table with Transaction Date as rows, Object as > columns, and counts of Transacations as data fields > 2. Click any date in the Transaction Date column > 3. Date | Group and Outline | Group > 4. Select the "Intervals" radio button under "Group By" > 5. Check the "Months" checkboxes under "Group By" > > This will group transaction data by month. If there are data from > multiple years, then also check the "Years" checkboxes under "Group By" > in the Group dialog box. This will avoid group data from every January > from every year in the same row (if that's not what you want). > > What was getting me stuck was that the Transaction Date must be in the > rows field. You cannot do this with Transaction Date in the columns > field, as I wanted to. > > Julian > > On 2021-05-19 9:12 p.m., Drew Jensen wrote: > > Well, the simple way. > > Select all of your Date cells with data and change the Cell format to > > display only the month portion of the date. > > When you create the pivot table, include the 'Transaction Date' column > > in both the Columns fields list and the Row fields list, along with > > Object. > > You could also set the Filters list to include Object if you wanted to > > look at Cars or Trucks. > > > > > > I hope that works for you, best wishes, > > > > Drew > > > > On Wed, May 19, 2021 at 3:57 PM Julian So wrote: > >> Hello, > >> > >> I have data like the following: > >> > >> Transaction Date | Object | Transaction > >> 2021-01-01 | Car | Bought > >> 2021-03-01 | Car | Bought > >> 2021-03-15 | Truck | Bought > >> 2021-03-16 | Car | Bought > >> 2021-03-18 | Car | Sold > >> 2021-04-01 | Truck | Sold > >> > >> I want to create a pivot table with Transaction Date as columns, Object > >> as rows, and counts of Transaction as data fields . However, I do not > >> want individual dates as columns, I want to look at the data by month. > >> In other words, instead of this: > >> > >> Object | 2021-01-01 | 2021-03-01 | 2021-03-15 | 2021-03-16 | 2021-03-18 > >> | 2021-04-01 > >> Car | 1 | 1 | 0 | 1 | 1 | > >> Truck | 0 | 0 | 1 | 0 | 0 | 1 > >> > >> I want this: > >> > >> Object | January | February | March | April > >> > >> Car | 1 | 0 | 3 | 0 > >> > >> Truck | 0 | 0 | 1 | 1 > >> > >> How do I do this in LibreOffice Calc? > >> > >> Julian > >> -- > >> > >> Julian So, B.Sc., > >> Director of Quality Assurance > >> Immuni T > >> 5th floor, suite SB-5180 > >> 141 President-Kennedy Avenue > >> Montreal QC, H2X 1Y4 > >> 514 545 3097 x105 Office > >> 514 545 3064 Fax > >> > >> Confidentialité: > >> Ce courrier électronique est confidentiel et protégé. Toute > >> diffusion, utilisation ou copie de ce message ou des renseignements > >> qu'il contient par une personne autre que le (les) destinataire(s) > >> désigné(s) est interdite. Si vous recevez ce courrier électronique > >> par erreur, veuillez informer l'expéditeur par courriel immédiatement > >> et effacer ce message et en détruire toute copie. > >> > >> Confidentiality: > >> This message is confidential and privileged. Any distribution, use or > >> copying of this e-mail or the information it contains by other than > >> an intended recipient is unauthorized. If you are not the addressee, > >> please inform the sender by return e-mail immediately and delete this > >> message and destroy all copies. > >> > >> > >> -- > >> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > >> Problems? > >> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > >> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > >> List archive: https://listarchives.libreoffice.org/global/users/ > >> Privacy Policy: https://www.documentfoundation.org/privacy > > -- > Julian So, B.Sc., > Director of Quality Assurance > Immuni T > 5th floor, suite SB-5180 > 141 President-Kennedy Avenue > Montreal QC, H2X 1Y4 > 514 545 3097 x105 Office > 514 545 3064 Fax > > Confidentialité: > Ce courrier électronique est confidentiel et protégé. Toute > diffusion, utilisation ou copie de ce message ou des renseignements > qu'il contient par une personne autre que le (les) destinataire(s) > désigné(s) est interdite. Si vous recevez ce courrier électronique > par erreur, veuillez informer l'expéditeur par courriel immédiatement > et effacer ce message et en détruire toute copie. > > Confidentiality: > This message is confidential and privileged. Any distribution, use or > copying of this e-mail or the information it contains by other than > an intended recipient is unauthorized. If you are not the addressee, > please inform the sender by return e-mail immediately and delete this > message and destroy all copies. > > > -- > To
Re: [libreoffice-users] Pivot Tables - Monthly subtotals
Actually, I figured out how to get what I wanted. This is the way: 1. Build the pivot table with Transaction Date as rows, Object as columns, and counts of Transacations as data fields 2. Click any date in the Transaction Date column 3. Date | Group and Outline | Group 4. Select the "Intervals" radio button under "Group By" 5. Check the "Months" checkboxes under "Group By" This will group transaction data by month. If there are data from multiple years, then also check the "Years" checkboxes under "Group By" in the Group dialog box. This will avoid group data from every January from every year in the same row (if that's not what you want). What was getting me stuck was that the Transaction Date must be in the rows field. You cannot do this with Transaction Date in the columns field, as I wanted to. Julian On 2021-05-19 9:12 p.m., Drew Jensen wrote: Well, the simple way. Select all of your Date cells with data and change the Cell format to display only the month portion of the date. When you create the pivot table, include the 'Transaction Date' column in both the Columns fields list and the Row fields list, along with Object. You could also set the Filters list to include Object if you wanted to look at Cars or Trucks. I hope that works for you, best wishes, Drew On Wed, May 19, 2021 at 3:57 PM Julian So wrote: Hello, I have data like the following: Transaction Date | Object | Transaction 2021-01-01 | Car | Bought 2021-03-01 | Car | Bought 2021-03-15 | Truck | Bought 2021-03-16 | Car | Bought 2021-03-18 | Car | Sold 2021-04-01 | Truck | Sold I want to create a pivot table with Transaction Date as columns, Object as rows, and counts of Transaction as data fields . However, I do not want individual dates as columns, I want to look at the data by month. In other words, instead of this: Object | 2021-01-01 | 2021-03-01 | 2021-03-15 | 2021-03-16 | 2021-03-18 | 2021-04-01 Car | 1 | 1 | 0 | 1 | 1 | Truck | 0 | 0 | 1 | 0 | 0 | 1 I want this: Object | January | February | March | April Car | 1 | 0 | 3 | 0 Truck | 0 | 0 | 1 | 1 How do I do this in LibreOffice Calc? Julian -- Julian So, B.Sc., Director of Quality Assurance Immuni T 5th floor, suite SB-5180 141 President-Kennedy Avenue Montreal QC, H2X 1Y4 514 545 3097 x105 Office 514 545 3064 Fax Confidentialité: Ce courrier électronique est confidentiel et protégé. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez informer l'expéditeur par courriel immédiatement et effacer ce message et en détruire toute copie. Confidentiality: This message is confidential and privileged. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you are not the addressee, please inform the sender by return e-mail immediately and delete this message and destroy all copies. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy -- Julian So, B.Sc., Director of Quality Assurance Immuni T 5th floor, suite SB-5180 141 President-Kennedy Avenue Montreal QC, H2X 1Y4 514 545 3097 x105 Office 514 545 3064 Fax Confidentialité: Ce courrier électronique est confidentiel et protégé. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez informer l'expéditeur par courriel immédiatement et effacer ce message et en détruire toute copie. Confidentiality: This message is confidential and privileged. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you are not the addressee, please inform the sender by return e-mail immediately and delete this message and destroy all copies. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Pivot Tables - Monthly subtotals
Well, the simple way. Select all of your Date cells with data and change the Cell format to display only the month portion of the date. When you create the pivot table, include the 'Transaction Date' column in both the Columns fields list and the Row fields list, along with Object. You could also set the Filters list to include Object if you wanted to look at Cars or Trucks. I hope that works for you, best wishes, Drew On Wed, May 19, 2021 at 3:57 PM Julian So wrote: > > Hello, > > I have data like the following: > > Transaction Date | Object | Transaction > 2021-01-01 | Car | Bought > 2021-03-01 | Car | Bought > 2021-03-15 | Truck | Bought > 2021-03-16 | Car | Bought > 2021-03-18 | Car | Sold > 2021-04-01 | Truck | Sold > > I want to create a pivot table with Transaction Date as columns, Object > as rows, and counts of Transaction as data fields . However, I do not > want individual dates as columns, I want to look at the data by month. > In other words, instead of this: > > Object | 2021-01-01 | 2021-03-01 | 2021-03-15 | 2021-03-16 | 2021-03-18 > | 2021-04-01 > Car | 1 | 1 | 0 | 1 | 1 | > Truck | 0 | 0 | 1 | 0 | 0 | 1 > > I want this: > > Object | January | February | March | April > > Car | 1 | 0 | 3 | 0 > > Truck | 0 | 0 | 1 | 1 > > How do I do this in LibreOffice Calc? > > Julian > -- > > Julian So, B.Sc., > Director of Quality Assurance > Immuni T > 5th floor, suite SB-5180 > 141 President-Kennedy Avenue > Montreal QC, H2X 1Y4 > 514 545 3097 x105 Office > 514 545 3064 Fax > > Confidentialité: > Ce courrier électronique est confidentiel et protégé. Toute > diffusion, utilisation ou copie de ce message ou des renseignements > qu'il contient par une personne autre que le (les) destinataire(s) > désigné(s) est interdite. Si vous recevez ce courrier électronique > par erreur, veuillez informer l'expéditeur par courriel immédiatement > et effacer ce message et en détruire toute copie. > > Confidentiality: > This message is confidential and privileged. Any distribution, use or > copying of this e-mail or the information it contains by other than > an intended recipient is unauthorized. If you are not the addressee, > please inform the sender by return e-mail immediately and delete this > message and destroy all copies. > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Pivot Tables - Monthly subtotals
Hello, I have data like the following: Transaction Date | Object | Transaction 2021-01-01 | Car | Bought 2021-03-01 | Car | Bought 2021-03-15 | Truck | Bought 2021-03-16 | Car | Bought 2021-03-18 | Car | Sold 2021-04-01 | Truck | Sold I want to create a pivot table with Transaction Date as columns, Object as rows, and counts of Transaction as data fields . However, I do not want individual dates as columns, I want to look at the data by month. In other words, instead of this: Object | 2021-01-01 | 2021-03-01 | 2021-03-15 | 2021-03-16 | 2021-03-18 | 2021-04-01 Car | 1 | 1 | 0 | 1 | 1 | Truck | 0 | 0 | 1 | 0 | 0 | 1 I want this: Object | January | February | March | April Car | 1 | 0 | 3 | 0 Truck | 0 | 0 | 1 | 1 How do I do this in LibreOffice Calc? Julian -- Julian So, B.Sc., Director of Quality Assurance Immuni T 5th floor, suite SB-5180 141 President-Kennedy Avenue Montreal QC, H2X 1Y4 514 545 3097 x105 Office 514 545 3064 Fax Confidentialité: Ce courrier électronique est confidentiel et protégé. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez informer l'expéditeur par courriel immédiatement et effacer ce message et en détruire toute copie. Confidentiality: This message is confidential and privileged. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you are not the addressee, please inform the sender by return e-mail immediately and delete this message and destroy all copies. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
