Dimension is MonthYear: How to count for the previous month?

[RoyBatty]

Hi,

I have the following data (just one table):

And on sheet I want to create a table that will have MonthYear as dimension and two measures:

• Current: Counts distinct users that had Activity = "Payment". Expression for this is: Count({<[Activity]={'Payment'}>} distinct [%_user_id]) and it works fine.
• Previous: Counts distinct users that had Activity = "Payment" during previous month. For example, if the value of the MonthYear dimension is Feb 2023, then it should show count for the Jan 2023. Or, for Jan 2023 it should show count for Dec 2022 ...

Do you know what the expression for the Previous measure would be?

I tried this:

Count({<MonthYear={'$(=Date(AddMonths(MonthYear, -1), 'MMM YYYY'))'}, [Activity]={'Payment'}>} distinct [%_user_id])

but it does not work.

Maybe such a measure is not possible in this particular case?

[rubenmarin1]

Hi, Set analysis is applied before calculating the table, so it doen't uses the dimension value.

The access the upper row you can use Above() function: https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRe...

Other solutions might be:

- Use an as-of calendar

- Create additional rows for past months so you can use an expression like Count(...[%_user_id_prev_month]).

- Have the data precalculated