Posted a question on Ask. Maybe someone here can help me, too, who doesn't look there.
https://ask.libreoffice.org/t/calc-getp ... acro/90758
For those who want a better understanding of the formula GETPIVOTDATA, I uploaded an example (I don't claim authorship, and I forgot the owner's name) that will make this post a little more interesting (I did not provide it on Ask).
I have a pivot table with a list of people and some total values for each. I need to go through the list and select only those for which some total values are less than zero in order to do further action.
What’s the best way to do it?
Handle the Pilot OutputRange directly on the sheet? In this case, the formula GETPIVOTDATA is useless.
Going through the entries on the sheet, I would know what to substitute in formula GETPIVOTDATA , but then it wouldn’t be necessary, because the total would be on the right side of the table row.
What are your thoughts? I’m leaning toward the idea of processing the range on the sheet with a separate procedure. Disadvantages: If the user changes the table view, the procedure will not work. So an additional check of the current table layout is required. This is also inconvenient.
I saw an example:
GETPIVOTDATA from a cell https://ask.libreoffice.org/t/macro-to- ... 51/5?=true
NOTE: That doesn’t work for me, because between the person’s name and the total value in the data field area, there are some values in the row field area that I have to enter into the formula to fully identify the entry. Otherwise, the formula won’t work because of the ambiguity of the data.
We are talking about fields that it is reasonable to display in the pivot table, but which are related to the person by a one-to-one relationship. But I have to put all these criterion pairs (field-item) into the formula. This adds nothing to the entry identification, but should be entered in the formula. However, the formula GETPIVOTDATA knows nothing about the relationship between fields and does exactly the right thing.
Excel has a corresponding method: Excel PivotTable.GetPivotData 2 https://learn.microsoft.com/en-us/offic ... tpivotdata
Because of the note above, it would still be inconvenient to use.
UPD. The pivot table interface seems to allow you to build and customize a pivot table, but not to retrieve related data. Or is it possible, using the pilot table API, to retrieve the required record with all related fields?
GETPIVOTDATA tutorial (thanks to the author):
Calc GETPIVOTDATA through macro
Calc GETPIVOTDATA through macro
- Attachments
-
- test_getpivot-modified.ods
- (52.43 KiB) Downloaded 90 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Re: Calc GETPIVOTDATA through macro
What seems to be tried to achieve in your example is the creation of 2D-organised tables based on some data elements. However, the formulas don't even access the pair of elements they should get values from a pivot table for. These entries in the header positions positions of the table are inserted literally again into the formulas.
As far as I understand the function GETPIVOTDATA() (which I never used before), is a way to use field names instead of indices to select values from a pivot table. Not actually an urgent thing. Where needed we can do it using MATCH(). What the purpose of calls to the function from running user code should be, is still very unclear to me.
I have generated a more real-world example, following the given example by using a donor-recipient situation.
Using the sample data and the associated pivot tables, it should now be easier to make the actual intentions clear.
You can access the pivot (DataPilotTables) identifying tzhem (e.g.) With the help of their output ranges, and you can work with the respective DataArray structures. However, this should not be an end in itself, but serve a purpose, and GETPIVOTDATA() applied via a FunctionAccess object may not play a role in it.
See attached example. (Yes. I know that related tasks are better solved with the help of databases.)
As far as I understand the function GETPIVOTDATA() (which I never used before), is a way to use field names instead of indices to select values from a pivot table. Not actually an urgent thing. Where needed we can do it using MATCH(). What the purpose of calls to the function from running user code should be, is still very unclear to me.
I have generated a more real-world example, following the given example by using a donor-recipient situation.
Using the sample data and the associated pivot tables, it should now be easier to make the actual intentions clear.
You can access the pivot (DataPilotTables) identifying tzhem (e.g.) With the help of their output ranges, and you can work with the respective DataArray structures. However, this should not be an end in itself, but serve a purpose, and GETPIVOTDATA() applied via a FunctionAccess object may not play a role in it.
See attached example. (Yes. I know that related tasks are better solved with the help of databases.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Calc GETPIVOTDATA through macro
Example-pivottable:
- Attachments
-
- example-pivotable.ods
- (75.77 KiB) Downloaded 88 times
-
- Снимок экрана от 2023-04-23 09-20-58.png (38.29 KiB) Viewed 1558 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Re: Calc GETPIVOTDATA through macro
Accounting software never uses spreadsheets as storage medium.
https://ask.libreoffice.org/uploads/sho ... DWOs1z.odb (includes [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes instead of a refresh button on form.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Calc GETPIVOTDATA through macro
Though I had to handle lots of data by "professionally" developed databases (and often had to fight problems), and was charged with a lot of things concerning IT at a time, I don't know every accounting software, and surely not what many people otherwise created as their actually working solutions (*).
Just tinkered a bit around in the sheets the questioner had attached.
I now think he tried to get the data for some prints per "debitor" with the help of the GETPIVOTDATA() function to automate serial printing. I still think that the mentioned function isn't really uf much use for the task, and I don't feel sure to generally have understood its purposes sufficiently.
For the sake of the Russian-German relations I attach the result hoping it might be of some use.
Editing 2023-04-24 about 13:15 UTC
I beg your indulgence for the superfluous remark above. I should have anticipated expectable comments.
(*) ...for small-scale-cases with some special requirements
Just tinkered a bit around in the sheets the questioner had attached.
I now think he tried to get the data for some prints per "debitor" with the help of the GETPIVOTDATA() function to automate serial printing. I still think that the mentioned function isn't really uf much use for the task, and I don't feel sure to generally have understood its purposes sufficiently.
I beg your indulgence for the superfluous remark above. I should have anticipated expectable comments.
(*) ...for small-scale-cases with some special requirements
Last edited by Lupp on Mon Apr 24, 2023 2:16 pm, edited 2 times in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Calc GETPIVOTDATA through macro
As a metaphor why they are about to lose their war?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice