How can create a filter in order to scope X axis in 12 months less than selected

[migueldfr_23]

Hello everyone,

I am struggling with this code.
Count({
<DateType={'active'},
CanonicalMonthName = {"$(=Date(AddMonths(Max({TrendLine}CanonicalMonthName), -12),'MMM YYYY'))"}
>} TOTAL DISTINCT person_id)

I would like to have the same value in every single month, because I will calculate the retention rate.

here, I have all the values but the plot show up all the months, I would like to see just -12 month than selected.

For example I can show up a plot, that works as I want.

Count({
<
DateType = {'active'},
IsFirstDayOfMonth = {1},
CanonicalMonthName = {">=$(=addmonths(Monthstart(max({TrendLine}CanonicalMonthName)),-12))<=$(=max({TrendLine}CanonicalMonthName))"}
>
} DISTINCT person_id)

The only thing is missing is the same value for all the months.

Thank you so much 

[migueldfr_23]

Here you have

Thanks a lot

[rubenmarin]

Hi, I added 2 charts, one using the condition on expression, and another using the calculated dimension.

[migueldfr_23]

Thank you a lot!

Is working, but I would like to have the value of the month select -12 months.

So, I select Jun 2025, I would like to have as repeat value Jun 2024, I am working on it also

Thanks

[rubenmarin]

Hi, just set 12 (or the value you want) to the addmonths or the Monthstart function

[migueldfr_23]

Hi, what do you mean ? 

Count({ < DateType = {'active'}, 

IsFirstDayOfMonth = {1}, 

    CanonicalMonthName = {">=$(=addmonths(Monthstart(max({TrendLine}CanonicalMonthName)),-12))<=$(=max({TrendLine}CanonicalMonthName))"} > } 

TOTAL DISTINCT person_id)

Now with this expression, if I select Jun 2025, the bar chart bring up the number of Jun 2025, and not Jun 2024 that it is the one I want.

Thanks

[rubenmarin]

HI, I've tested and setting '12' it shows Jun-24

[migueldfr_23]

But I gues this value is from Jun 2024.
In my example when I select in this case that it is more near to Sep 2025 and it show up the value of this month.

[migueldfr_23 · Accepted solution]

This is what solved my issue finally.

1-

((Count({
       <DateType={'active'}, IsFirstDayOfMonth={1} >}
DISTINCT person_id)

- 

Count({ < DateType = {'active'}, 
	IsFirstDayOfMonth = {1}, 
    CanonicalMonthName = {">=$(=addmonths(Monthstart(max({TrendLine}CanonicalMonthName)),-12))<=$(=addmonths(Monthend(max({TrendLine}CanonicalMonthName)),-12))"} > } 
TOTAL DISTINCT person_id))  

/

Count({ < DateType = {'active'}, 
	IsFirstDayOfMonth = {1}, 
    CanonicalMonthName = {">=$(=addmonths(Monthstart(max({TrendLine}CanonicalMonthName)),-12))<=$(=addmonths(Monthend(max({TrendLine}CanonicalMonthName)),-12))"} > } 
TOTAL DISTINCT person_id))

But I has to be line chart, but when I change the plot to line chart, this is how it show up:

I t has to be from Descent and also MonthYear and not dates.

Hope somebody can help me out

Thank you 

[rubenmarin]

Hi, about the line chart, in x axis, disable the continuous axis.

If needed, then you can change the sort options.