topic Re: How can create a filter in order to scope X axis in 12 months less than selected in App Development

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

migueldfr_23 — Tue, 02 Sep 2025 10:02:12 GMT

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

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

rubenmarin — Wed, 03 Sep 2025 06:54:31 GMT

Hi, have you tried using TOTAL?

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

migueldfr_23 — Wed, 03 Sep 2025 08:28:31 GMT

Hello @rubenmarin ,
In fact I am using this code, which is the one I have struggling with :

Count({ <DateType={'active'}, CanonicalMonthName = {"$(=Date(AddMonths(Max({TrendLine}CanonicalMonthName), -12),'MMM YYYY'))"} >} TOTAL DISTINCT person_id)

this is how it looks:

But I would like to scope the bars along just 12 months, not all of them.

Hope htis can better explain my purpose.

Thank you

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

rubenmarin — Wed, 03 Sep 2025 08:39:26 GMT

Hi, that one doesn't have the same set analysis for CanonicalMonthName (or IsFirstDayOfMonth). to retrieve data for the last 12 months you'll need to change the set analysis.

The expression I posted is the one you said it works but you want the same value for all the months, so I just added the TOTAL to the expression. Have you tested that one?

Another option could be to enclose everything in an If that checks the month, like:

If(CanonicalMonthName>=MonthStart(Today(),-11), [YourExpression])

Maybe you need to have the 'else' part as 0 and hide zero values.

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

migueldfr_23 — Wed, 03 Sep 2025 08:48:29 GMT

Yes, you are right.
I totally forgot to see the IsFirstDayOfMonth filter.
With the expression you posted, this is what show up :

Thanks

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

rubenmarin — Wed, 03 Sep 2025 08:54:15 GMT

Ok, that looks like the CanonicalMonthName filter is not working, In tour first post you said that worked, just tjat it doens't shows the same value for all months, so if you recreted that same chart, cehck that it filters the last 12 months, then add the TOTAL to the expression... it still filters 12 months or just by adding the total it shows all the months?

Also, have you tested the If() option I posted before?

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

migueldfr_23 — Wed, 03 Sep 2025 09:07:23 GMT

When I use this expression:

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

CanonicalMonth range is working well, because is 12 months in X axis.

In the other hand, when I use this code

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

So, it is the same code with or without TOTAL and the change is way different.

So I cannot see what is happening now.

if I use if() clausure this is the trigger, having selected JUN 2025:

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

Thank you

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

rubenmarin — Wed, 03 Sep 2025 09:16:44 GMT

Ops, I used Today() instead of the selected month, maybe:

If(CanonicalMonthName>=MonthStart(Max({TrendLine} CanonicalMonthName),-11) and CanonicalMonthName<=Max({TrendLine} CanonicalMonthName, ...)

Or another option could be using a calculated dimension as:

Aggr(If(CanonicalMonthName>=MonthStart(Max({TrendLine} CanonicalMonthName),-11) and CanonicalMonthName<=Max({TrendLine} CanonicalMonthName), CanonicalMonthName)

and unchecking the option to show null values on dimension.

I don't know about the state {TrendLine}, or the data behind, maybe it needs some adjustments to return the expected values, you can create a table with CanonicalMonthName as dimension and "If(CanonicalMonthName>=MonthStart(Max({TrendLine} CanonicalMonthName),-11) and CanonicalMonthName<=Max({TrendLine} CanonicalMonthName, 1,0)" as expression and check if the 1 is on the expected months, and adjust until it works

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

migueldfr_23 — Wed, 03 Sep 2025 09:50:07 GMT

Thanks.

We use the IF with two condition:

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

And TrendLine state is just a filter with this box filter in order to do not mix with another sheets.

If I can, I would like to avoid more table , because this reporte has a plenty of tables, but if just if we can avoid it.

Thank you

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

rubenmarin — Wed, 03 Sep 2025 09:53:20 GMT

Can you upload a sample with some dummy data so I can make some tests?

As said in my previous post:

maybe it needs some adjustments to return the expected values, you can create a table with CanonicalMonthName as dimension and "If(CanonicalMonthName>=MonthStart(Max({TrendLine} CanonicalMonthName),-11) and CanonicalMonthName<=Max({TrendLine} CanonicalMonthName, 1,0)" as expression and check if the 1 is on the expected months, and adjust until it works

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

migueldfr_23 — Wed, 03 Sep 2025 11:29:00 GMT

Here you have

Thanks a lot

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

rubenmarin — Wed, 03 Sep 2025 11:40:38 GMT

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

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

migueldfr_23 — Wed, 03 Sep 2025 13:09:32 GMT

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

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

rubenmarin — Thu, 04 Sep 2025 06:01:48 GMT

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

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

migueldfr_23 — Thu, 04 Sep 2025 09:19:17 GMT

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

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

rubenmarin — Thu, 04 Sep 2025 09:25:00 GMT

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

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

migueldfr_23 — Thu, 04 Sep 2025 09:58:54 GMT

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.

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

migueldfr_23 — Thu, 04 Sep 2025 13:29:26 GMT

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

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

rubenmarin — Fri, 05 Sep 2025 06:10:53 GMT

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

If needed, then you can change the sort options.