CODEWITHSUNDEEP
qlikviewqliksenseqlik-expression
PeraMika
PeraMika

Reputation: 3678

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

I have the following data (just one table):

enter image description here

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

enter image description here

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?

Upvotes: 2

Views: 263

Answers (1)

Humpelstielzchen
Humpelstielzchen

Reputation: 6441

Preparing some dummy data for myself:

Data:
Load *, 
    Date(MonthStart([Fact Date]), 'MMM YYYY') as [Month Year], 
    RowNo() as %_fact_id
Inline [
%_user_id, Activity, "Fact Date" 
1, Registration,    2023-01-01 
1, Payment,         2023-01-30 
2, Registration,    2023-01-10 
2, Payment,         2023-01-14 
2, Payment,         2023-02-23 
3, Registration,    2023-01-04 
3, Payment,         2023-01-26 
4, Registration,    2023-02-07 
5, Registration,    2023-02-19 
5, Payment,         2023-02-20 
5, Payment,         2023-02-23 
];

You can use this expression using Aggr() and Above() instead of Previous()

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

Returning this result:

enter image description here

Upvotes: 1

Related Questions