QuickSight count new invoiced users per month

Question

My dataset is a list of user invoices.

I can easily show how many invoices I have per month (aggregate the invoice date per month).

Now how can I show per month how many new users are invoiced?

Let's say I have the following data

I have 1 invoice in October and 2 in November.

I have 1 new invoiced user in October and 1 new invoiced user in November.

Edit: Basically I would like to filter out invoices and keep only the first one for each user.

occamatic · Answer 1 · 2021-11-15T11:59:13.710

1

You can do this with the difference calculated field, from your example I think I can get what you mean, I can make a table like the following

date	difference	count(user_id)
Oct		1
Nov	1	2

Using the following formula:

difference
(
     count({user_id}),
     [ {date} ASC ],
     1
)

I realize this skips out the 1 for October but this should only happen for the first month that the analysis shows

edited Nov 15 '21 at 11:59

answered Nov 15 '21 at 10:00

occamatic

Ok I see what you mean there. Basically it calculates the variation of total users invoiced month by month. That works fine, in my question though I was more interested into the number of new users only, excluding those who are not invoiced anymore. It's slightly different. – D0m3 Nov 17 '21 at 14:15
Yes I see the difference, your own answer is much better – occamatic Nov 17 '21 at 15:02

score 1 · Answer 2 · answered Nov 17 '21 at 14:38

I think I managed to find a way thanks to @occamatic and another answer there:

I can add a calculated field like is_first_invoice:

ifelse( minOver({date}, [{user_id}] , PRE_AGG) = {date}, 1, 0)

Then in my analysis I add a filter to exclude 0 values for this field. I am left with only first invoices.

2 Answers2