Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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
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
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,
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
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!
what is your expected output? I am getting 325.
Regards,
The correct answer is the same, 175. Customer "CDddd" has no invoices in 2018, so you need to ignore