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,

[uacg0009 · Accepted solution]

Hi Gabri,

The first idea in my mind is in below, I think that's what you want, but I think it must have better solution:

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

Aiolos Zhao

[PrashantSangle]

try below set analysis.

Count({<CLINAME={"=(Count(CLINAME)>1)"}>}distinct CLINAME)

Regards,
Prashant Sangle

[Gabri]

Thanks Uacg0009, your answer was very good!

Today I want to complicate the function to get the TOTAL Amount of this (2) CLINAME, in this case:

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'

REPETITIVE CUSTOMER (2019)    2    'solved'
TOTAL AMOUNT   (2019)  225    'solved'
TOTAL AMOUNT (2019) Repetitive customer    175  'I don't know how to get this value'

Thanks in advance,

[PrashantSangle]

Try below

Sum({<CLINAME={"=(Count(CLINAME)>1)"}>}AMOUNT)

Regards,
Prashant Sangle

[Gabri]

Hi PrashantSangle, thanks for your answer, but is not correct, your expression return the sum of amounts of clients that has more than 1 invoice in 2019.

I need ( the sum of amounts of repetitive customers)

Repetitive customers (CA, Cccc)

TOTAL AMOUNT (2019) Repetitive customer 175

Thanks

[PrashantSangle]

Hi,

I have tried the same expression in my qvf and when I select 2019, It is giving me 175.

Regards,

[Gabri]

I'm sorry, can you try with this table?

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	3	50
CDddd	2019	2	50
CDddd	2019	1	50

Thanks!

[PrashantSangle]

what is your expected output? I am getting  325.

Regards,

[Gabri]

The correct answer is the same, 175. Customer "CDddd" has no invoices in 2018, so you need to ignore