Previous period comparison with a filter on date does not work

Question & Answer
, , , ,
1

Hi,

I work for a software company which sells subscriptions to its apps to its customers. There are more than 10 apps in total. I have a dataset with tracking informations from customers and in order to understand better who uses the apps I have made an analysis in Quicksight in order to display some KPIs.

So I created a table visual with on the line dimension the apps which my company sells. On the columns some KPIs like : how many active users for the period, how many users which connect once a month, new users for the period etc. The visual is filtered with a “date between” filter.

My problem is : I need to display a column with the previous period comparison for some of the KPIs, for example if I set the date filter between 1st of JUNE and 30th of JUNE, then I need a column displaying the KPIs ( for example “nb of users active for the period”) for MAY.

I tried to use a calculated field like “periodOverPeriodLastValue(distinct_count({user_id}), date, MONTH, 1)” I also tried to McGiver something with parameters but nothing works for the moment.

If someone could help me I would be very grateful.
Thank you

2

Hi,

Can modify the custom SQL as shown below,

select A.New_users_count,B.New_users_count,
A.Active_users_count,B.Active_users_count
from Table A --Prior
inner join Table B on dateadd(month,-1,B.date_col) = A.date_col

Hope it helps !

1 Like
3

Hi, @Vignesh_Kannan
Thank you for your reply,
but is there a way to do this with a calculated field ?
Because the thing is that the fields “new user count”, “active user count” etc are already calculated fields that I made.
To clarify my problem my raw dataset that I used for the analysis is structured as followed :

  • app_name

string

  • event

string

  • user_type

string

  • user_id

bigint

  • date

date

  • hour

bigint

  • nb of events

bigint

  • user_date_first_connection

date

It’s a tracking table where each line corresponds to one event made by an user on a particular app and a particular date and on a particular hour of this date. I made the KPIs from this.

4

Hi @Paul1

Are your calculations already aggregated by month?

If so, have you considered using a lag function ordered by date time month and partitioned by app type?