Using pivot table groups in set analysis

[aguitatierra]

I have a pivot table showing the reservations made through various providers for different months/years. Now, I would like to further filter this data based on the date when the sale was made. I want to show sales made until the current day of each year. For instance, if I'm filtering November 2022-2023 and today is 20/9/2023, I want:

• column 2022 to show the reservations for November 2022 with the condition SalesDate <= 20/9/2022
• column 2023 tow show the reservations for November 2023 meeting SalesDate <= 20/9/2023

Until now this is my code, but I can't make it work properly.

count({$<SalesDate = {"<=$(=MakeDate($(=Year), Num(Month(Today())), Day(Today())))"}>} Paxes)

Many thanks!!

[L_Hop]

Hi  Aguitatierra ,

you may add some date ranges to set analysis.

For 2022; count({$<SalesDate = {"<=$(=today())>=$(=YearStart(today()))"}>}Paxes )

For 2023; count({$<SalesDate = {"<=$(=AddMonths(today(),-12))>=$(=YearStart(AddMonths(today(),-12)))"}>}Paxes)

regards

[aguitatierra]

Hi L_Hop,

Thanks for your reply! The idea is that the results come from just one formula (there isn't a formula for 2021 and another for 2022. I actually have many more years). The pivot table splits the data by year.