How can create a filter in order to scope X axis i... - Qlik Community

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

rubenmarin1

Hi, have you tried using TOTAL?

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

migueldfr_23

Hello @rubenmarin1 ,
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

rubenmarin1

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.

migueldfr_23

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

Thanks

rubenmarin1

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?

migueldfr_23

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

rubenmarin1

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

migueldfr_23

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

rubenmarin1

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

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

General Question