Dimension is Month Year: How to count for the previous month?

[RoyBatty]

Hi,

I have the following data (just one table):

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" ...

Load Script:

Spoiler

[Users-Registration]:
Load * Inline
[
ID, NAME, BALANCE, REGISTERED
1, A1 A1, 10, 2023-01-01 06:38:36
2, A2 A2, 10, 2023-01-02 06:38:36
3, A3 A3, 10, 2023-02-15 06:38:36
4, A4 A4, 10, 2023-02-15 06:38:36
5, A5 A5, 10, 2023-02-15 06:38:36
6, A6 A6, 10, 2023-02-31 06:38:36
]
;

[Payments]:
Load * Inline
[
ID, USER_ID, AMOUNT, CREATED_AT
1, 1, 20, 2023-01-31 06:38:36
2, 2, 20, 2023-02-01 06:38:36
3, 3, 20, 2023-02-28 06:38:36
]
;

[Costs]:
Load * Inline
[
ID, USER_ID, COST, CREATED_AT

]
;

/*
* Break out the users table into a single table
*
*/
[Users]:
Load ID As 'User Id',
ID As '%_user_id',
NAME
Resident [Users-Registration]
;

/*
* Create a Fact Table with cleaned up dates from the timestamps
*
*/
[Fact Table]:
Load ID As '%_user_id',
'Registration' As 'Activity',
Date(Floor(TimeStamp#(REGISTERED, 'YYYY-MM-DD h:mm:ss')), 'YYYY-MM-DD') As 'Fact Date',
Date(MonthStart(Date#(SubField(REGISTERED, ' ', 1), 'YYYY-MM-DD')), 'MMM YYYY') As MonthYear
Resident [Users-Registration]
;

Concatenate([Fact Table])
Load ID As 'Payment Id',
USER_ID As '%_user_id',
AMOUNT,
'Payment' As 'Activity',
Date(Floor(TimeStamp#(CREATED_AT, 'YYYY-MM-DD h:mm:ss')), 'YYYY-MM-DD') As 'Fact Date',
Date(MonthStart(Date#(SubField(CREATED_AT, ' ', 1), 'YYYY-MM-DD')), 'MMM YYYY') As MonthYear
Resident [Payments]
;

Concatenate([Fact Table])
Load ID As 'Cost Id',
USER_ID As '%_user_id',
COST as AMOUNT,
'Cost' As 'Activity',
Date(Floor(TimeStamp#(CREATED_AT, 'YYYY-MM-DD h:mm:ss')), 'YYYY-MM-DD') As 'Fact Date',
Date(MonthStart(Date#(SubField(CREATED_AT, ' ', 1), 'YYYY-MM-DD')), 'MMM YYYY') As MonthYear
Resident [Costs]
;

/*
* Create a unique id for each row of the Fact Table
*
*/
[Fact Table 2]:
Load [Fact Id] As '%_fact_id',
*
;
Load RecNo() As 'Fact Id',
*
Resident [Fact Table]
;

Drop Table [Fact Table]
;

Rename Table [Fact Table 2] To [Fact Table]
;

Drop Tables [Users-Registration], [Payments], [Costs]
;

Exit Script
;

Can you help me solve this (for the "Previous" measure, to show the number of distinct users who had "Activity" = "Payment" during the previous month)? I searched the forum but could not find a solution for this problem (I found this thread: https://community.qlik.com/t5/App-Development/Previous-month-count-on-selected-month/td-p/1819109  but it didn't work for me, I didn't know how to apply it).

[Lisa_P]

Above(Count({<[Activity]={'Payment'}>} distinct [%_user_id]) )

will work except for first value

[RoyBatty]

Sorry, I didn't understand - what do you mean by that? I tested it and it works fine.

However, for "Previous" measure - I don't know how to do it.

[RoyBatty]

If I add Date(AddMonths(MonthYear, -1), 'MMM YYYY') to be the new dimension, it would show the correct values:

So Date(AddMonths(MonthYear, -1), 'MMM YYYY') works fine so I tried using it in the expression for the "Previous" measure.

First I tried:

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

but it doesn't work. Then I saw a post on this thread where it is recommended to create a variable... so I tried something similar:

I created a new variable vPreviousMonth with the following definition: =Date(AddMonths(MonthYear, -1), 'MMM YYYY'), and then I tried Count({<MonthYear={"$(vPreviousMonth)"}, [Activity]={'Payment'}>} distinct [%_user_id]) - but that doesn't work either.