topic Re: Repetitive customer by year in App Development

Repetitive customer by year

Gabri — Fri, 02 Apr 2021 07:47:40 GMT

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,

Re: Repetitive customer by year

uacg0009 — Wed, 13 Feb 2019 09:05:14 GMT

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

Re: Repetitive customer by year

PrashantSangle — Wed, 13 Feb 2019 09:16:05 GMT

try below set analysis.

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

Regards,
Prashant Sangle

Re: Repetitive customer by year

Gabri — Thu, 14 Feb 2019 11:04:05 GMT

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,

Re: Repetitive customer by year

PrashantSangle — Thu, 14 Feb 2019 10:32:05 GMT

Try below

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

Regards,
Prashant Sangle

Re: Repetitive customer by year

Gabri — Thu, 14 Feb 2019 11:06:16 GMT

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

Re: Repetitive customer by year

PrashantSangle — Thu, 14 Feb 2019 11:05:04 GMT

Hi,

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

Regards,

Re: Repetitive customer by year

Gabri — Thu, 14 Feb 2019 11:11:42 GMT

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!

Re: Repetitive customer by year

PrashantSangle — Thu, 14 Feb 2019 11:17:15 GMT

what is your expected output? I am getting 325.

Regards,

Re: Repetitive customer by year

Gabri — Thu, 14 Feb 2019 11:24:20 GMT

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

Re: Repetitive customer by year

Gabri — Thu, 14 Feb 2019 15:06:55 GMT

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

Re: Repetitive customer by year

uacg0009 — Fri, 15 Feb 2019 07:19:55 GMT

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

Re: Repetitive customer by year

Gabri — Mon, 18 Nov 2019 14:50:07 GMT

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!