Set Analysis for last 12 months

[Lalita_Rai]

Hi, I need set analysis for

1. last 12 months.

2. for every year

How do I do it?

Thank you.

[Andrei_Cusnir · Accepted solution]

Hello,

If my understanding is correct then for the first point you are looking for something like this: 

Sum({<Date={">$(=MonthStart(AddMonths(Today(), -11)))"}>}Value)

This expression sums the field Value for all the dates grater than 11 months older than the current one. Today() will return "14/12/2021" therefore going back 11 months will return "14/1/2021". Adding the MonthStart() function will give us "1/1/2021". If current moth was November, then the result would have been "1/12/2020" etc. You can modify this expression based on your needs. For example, if you are looking for 12 months before the last date in the dataset, then you will replace Today() with Max(Date) etc.

Regarding your second part, can you please provide a context in which you need this set analysis? Are you looking to create a table where you are going to display all the years and then all the sums of the values for those years? 

[Lalita_Rai]

Hi, Andrei,

Thank you for the first answer. That helped a lot.

With regards to the second question, I might have misunderstood it.Just
ignore it.

Regards,
Lalita