Calc 7.2 GETPIVOTDATA not working on grouped date field

[mackinlay53]

I’m trying to extract data from a pivot table where there is a grouped Date field (grouped by Months) in the column heading row. When I write the formula by hand I get a #REF! error. This file was imported from Excel where the GETPIVOTDATA was working…and the current file remains in .ods format after importing from .xlsx.

The hand written formula returns #REF!.
=GETPIVOTDATA($B$7,“Amount”,“Category”,“Cash”,“Months”,K$3)
FYI k$3 contains “Mar” (i.e. March).

The imported formulas report Err:508.
=IFERROR(GETPIVOTDATA(“Amount”,$B$7,“Date”,AK$8,“Category”,TableBudget2#this row,category,“Years”,2021),0)
FYI, AK$8 contains “Mar”

Can anyone please help me correct these formulas? I’ve tried lots of variations without success.

Also, in Excel there is an option to “Generate GETPIVOTDATA()” formulas. Does something like this exist in LO Calc?

Thank You,
…Patrick

[Villeroy]

Welcome to the forum. A document tells more than 1000 words.
getpivotdata.ods (57.3 KB)
GETPIVOTDATA only gets values that are visible in the referenced pivot. When you remove the year subtotal from my pivot, you get a #REF error for the year formula.

[mackinlay53]

Thank you, this file was indeed worth more than 1000 words.
Your tip helped me solve my problem.
That really makes my day!

Thank You,
…Patrick

[mariosv]

Please mark Villeroy’s answer as solution.

[Villeroy]

Meanwhile, I learned how to use the second syntax variant of that function:
getpivotdata2.ods (71.1 KB)