Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data (just one table):
I want to create a table that will have MonthYear as dimension and two measures:
Load Script:
[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).
Above(Count({<[Activity]={'Payment'}>} distinct [%_user_id]) )
will work except for first value
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.
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.