Repetitive customer by year

[Gabri]

Hello community,

I have a question with a KPI function and I would like to know if you can help me.

I want to know how many clients, specified one year, have bought us in the previous year, for example:

CLINAME	YEAR	INVOICE NUM	AMOUNT
CA	2019	5	40
CA	2019	4	40
CA	2019	3	22
CA	2019	2	10
CA	2019	1	15
CA	2018	1	10
CBb	2018	2	20
CBb	2018	1	20
CCcc	2019	2	28
CCcc	2019	1	20
CCcc	2018	1	10
CCcc	2017	1	10
CDddd	2019	1	50

YEAR    2019    
        
TOTAL CLIENTS (2019)    3  'solved'
TOTAL INVOICES (2019)   8    'solved'
TOTAL AMOUNT   (2019)  225    'solved'
        
Repetitive customer (2019)    2    'I don't know how to get this value'

Thanks in advance,

[Gabri]

Now I'm trying diferent options, another example with a table, also need the (sum) of this 2 fiels...

[uacg0009 · Accepted solution]

Hi Gabri,

Please try below expression, it works in my desktop:

=Sum(if(aggr(NODISTINCT sum({<YEAR = {"$(=Only(YEAR) - 1)"}>}AMOUNT) > 0,CLINAME),AMOUNT))

Aiolos Zhao

[Gabri]

Hi, I want to add another field in this exercise, for example if I filter by YEAR=2019 this table

CLINAME	YEAR		AMOUNT
CA	2019		40
CA	2019		40
CA	2019		22
CA	2019		10
CA	2019		15
CA	2018		10
CBb	2018		20
CBb	2018		20
CCcc	2019		28
CCcc	2019		20
CCcc	2018		10
CCcc	2017		10
CDddd	2019		50

HOW MANY CUSTOMER ARE REPETITIVE ON FILTERED YEAR? 2 --> (CA, CCcc)

=Count(DISTINCT {$<YEAR = {"$(=Only(YEAR) - 1)"}>} if(aggr(sum(AMOUNT) > 0,CLINAME),CLINAME))

TOTAL AMOUNT OF REPETITIVE CUSTOMERS ON FILTERED YEAR. 175 --> (CA=127 + CCcc=48)

=Sum(if(aggr(NODISTINCT sum({<YEAR = {"$(=Only(YEAR) - 1)"}>}AMOUNT) > 0,CLINAME),AMOUNT))

 TOTAL INVOICES OF REPETITIVE CUSTOMERS ON FILTERED YEAR? 7 --> (CA=5, CCcc=2)

??? my option...

=Count(if(aggr(NODISTINCT sum({<YEAR= {"$(=Only(YEAR) - 1)"}>}AMOUNT) > 0,CLINAME),CLINAME))

Thanks in advance!