Hello,
I have a PivotTable with 'Contract signing quarter' as a filter, populated with a list of quartrs, in YYYY/QQ format (i.e., 2020/Q1, 2020/Q2 etc.)
Now, I have a Macto that refresh the Pivot based on x past quarters, starting from the last quarter. Fo example, if I would like to retrieve the latest 5 quarters on 11-Apr-2022, so I would like to filter the pivot based o:
2021/Q1, 2021/Q2, 2021/Q3, 2021/Q4, 2022/Q1.
From some reason, the below code does not work. I debugged (Add watch) the 'arr_Data_Model', and it retreived for the correct past x quarters. However, I still have no clue why the Pivot is not updated.
Additoinaly: a simplify code is much appreciated.
Sub Refresh_Pivot()
'Analyze the recent x quarters
Array_Counter = 0
Set ptt = ActiveCell.PivotTable
Case_Quarter = ""
Filter_Action = 5 '# of recent quarters
For Start_Year = 0 To Filter_Action - 1
DT = DateValue(Date)
EOM = Application.EoMonth(DT, (-Start_Year) * 12)
x = Format(EOM, "mm/dd/yyyy")
Which_Year2 = Year(EOM)
For I = 0 To Val(Right(currentquarter, 1)) - 1 'Past i quarters (+1 quarter)
DT = DateValue(Date)
EOM = Application.EoMonth(DT, ((-I - 1) * 3))
x = Format(EOM, "mm/dd/yyyy")
Case_Quarter = Which_Year2 & "/Q" & DatePart("q", EOM)
Case_Quarter_Data_MOdel = "[kpidatabase].[Contract signing quarter].&[" & Case_Quarter & "]"
Array_Counter = Array_Counter + 1
arr_ay(Array_Counter) = CStr(Case_Quarter)
arr_Data_Model(Array_Counter) = CStr(Case_Quarter_Data_MOdel)
Next
Next
'Refresh Pivot
For Each PTItm In ptt.PivotFields(Filter_Criteria).PivotItems
If Not IsError(Application.Match(PTItm.Caption, arr_ay, 0)) Then ' check if current item is not in the filter array
PTItm.Visible = True
Else
PTItm.Visible = False
End If
Next PTItm
End Sub
I have a PivotTable with 'Contract signing quarter' as a filter, populated with a list of quartrs, in YYYY/QQ format (i.e., 2020/Q1, 2020/Q2 etc.)
Now, I have a Macto that refresh the Pivot based on x past quarters, starting from the last quarter. Fo example, if I would like to retrieve the latest 5 quarters on 11-Apr-2022, so I would like to filter the pivot based o:
2021/Q1, 2021/Q2, 2021/Q3, 2021/Q4, 2022/Q1.
From some reason, the below code does not work. I debugged (Add watch) the 'arr_Data_Model', and it retreived for the correct past x quarters. However, I still have no clue why the Pivot is not updated.
Additoinaly: a simplify code is much appreciated.
Sub Refresh_Pivot()
'Analyze the recent x quarters
Array_Counter = 0
Set ptt = ActiveCell.PivotTable
Case_Quarter = ""
Filter_Action = 5 '# of recent quarters
For Start_Year = 0 To Filter_Action - 1
DT = DateValue(Date)
EOM = Application.EoMonth(DT, (-Start_Year) * 12)
x = Format(EOM, "mm/dd/yyyy")
Which_Year2 = Year(EOM)
For I = 0 To Val(Right(currentquarter, 1)) - 1 'Past i quarters (+1 quarter)
DT = DateValue(Date)
EOM = Application.EoMonth(DT, ((-I - 1) * 3))
x = Format(EOM, "mm/dd/yyyy")
Case_Quarter = Which_Year2 & "/Q" & DatePart("q", EOM)
Case_Quarter_Data_MOdel = "[kpidatabase].[Contract signing quarter].&[" & Case_Quarter & "]"
Array_Counter = Array_Counter + 1
arr_ay(Array_Counter) = CStr(Case_Quarter)
arr_Data_Model(Array_Counter) = CStr(Case_Quarter_Data_MOdel)
Next
Next
'Refresh Pivot
For Each PTItm In ptt.PivotFields(Filter_Criteria).PivotItems
If Not IsError(Application.Match(PTItm.Caption, arr_ay, 0)) Then ' check if current item is not in the filter array
PTItm.Visible = True
Else
PTItm.Visible = False
End If
Next PTItm
End Sub
