Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have this data (included below).
I want to find get a count of Customer_Account_ID that in the last 12 month that occurred more than 1 time. (Answer: 4 because customer Account ID 1, 4 and 8 only occurred once in the last 12 months )
(this expression is not working):
=(Count(Aggr(ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -12))"},
CUSTOMER_ACCOUNT_ID = {"=Count(CUSTOMER_ACCOUNT_ID) >= 2"}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID))) - I am not getting the right answer
Then for all customers in the last 12 months that occurred one time - If they occurred at least one time in the last 2 years I want to get a sum. (Answer is 2, because out of 1,4,8 - only 4 and 8 appeared in the last 36 months)
=if(AGGR(ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -12))"},
CUSTOMER_ACCOUNT_ID = {"=Count(CUSTOMER_ACCOUNT_ID) = 1"}>}distinct CUSTOMER_ACCOUNT_ID)
=ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -36))<$(=AddMonths(MonthStart(Today()), 12))"}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID)
This is to calculate all account with 1 transaction count:
count (AGGR(
ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -12))"},
CUSTOMER_ACCOUNT_ID = {"=Count(CUSTOMER_ACCOUNT_ID) = 1"}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID))
=(Count(Aggr(ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -35))<$(=AddMonths(MonthStart(Today()), -11))"}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID)))
The final answer would be 6.
4 from two of more transaction from current year
and
2 or more from all transaction that had 1 transaction in the last 12 months but appeared at least one more time in the last 36 months.
Any guidance will help.
Transaction Date | CUSTOMER_ACCOUNT_ID |
1/1/2014 | 10 |
2/1/2014 | 6 |
3/1/2014 | 4 |
4/1/2014 | 7 |
5/1/2014 | 4 |
6/1/2014 | 4 |
7/1/2014 | 4 |
8/1/2014 | 7 |
9/1/2014 | 2 |
10/1/2014 | 2 |
11/1/2014 | 4 |
12/1/2014 | 3 |
1/1/2015 | 2 |
2/1/2015 | 8 |
3/1/2015 | 7 |
4/1/2015 | 4 |
5/1/2015 | 8 |
6/1/2015 | 3 |
7/1/2015 | 5 |
8/1/2015 | 3 |
9/1/2015 | 6 |
10/1/2015 | 8 |
11/1/2015 | 9 |
12/1/2015 | 4 |
1/1/2016 | 7 |
2/1/2016 | 10 |
3/1/2016 | 4 |
4/1/2016 | 7 |
5/1/2016 | 9 |
6/1/2016 | 6 |
7/1/2016 | 6 |
8/1/2016 | 7 |
9/1/2016 | 8 |
10/1/2016 | 4 |
11/1/2016 | 10 |
12/1/2016 | 10 |
1/1/2017 | 1 |
2/1/2017 | 10 |
3/1/2017 | 9 |
Okay, I think I understand what you're asking now...
I think the second part should be:
=count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])=1'}*{'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -35))<$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>0'}>}distinct CUSTOMER_ACCOUNT_ID)
Then you would just add the above to what I gave you previously:
=count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID)
Azmina Virani wrote:
I want to find get a count of Customer_Account_ID that in the last 12 month that occurred more than 1 time. (Answer: 4 because customer Account ID 1, 4 and 8 only occurred once in the last 12 months )
=count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID)
Azmina Virani wrote:
Then for all customers in the last 12 months that occurred one time - If they occurred at least one time in the last 2 years I want to get a sum. (Answer is 2, because out of 1,4,8 - only 4 and 8 appeared in the last 36 months)
=count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -35))"}>}[Transaction Date])=1'}>}distinct CUSTOMER_ACCOUNT_ID)
I've also attached an example file. Let me know if it fixes the issue.
Almost -
I still am not able to get the last part.
=count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID)
The expression above gives a distinct count of all Customer_Account_ID that had more than 1 transaction.
The second part of - for all the transaction that that 1 transaction count - if the occur in the last 36 month as well - I want to add that count to the initial expression.
Example: (I changed the date 1/1/2016 from 7 to 5.)
There are 4 customers who did more that 1 transaction in the last 12 months. Customer 1, 4, 8 did only one.
Now if customer 1,4 and 8 did any transaction between 13-36 month from now - I want add that customer to my original count of 4.
Does this explain?
Transaction Date
CUSTOMER_ACCOUNT_ID | |
1/1/2014 | 10 |
2/1/2014 | 6 |
3/1/2014 | 4 |
4/1/2014 | 7 |
5/1/2014 | 4 |
6/1/2014 | 4 |
7/1/2014 | 4 |
8/1/2014 | 7 |
9/1/2014 | 2 |
10/1/2014 | 2 |
11/1/2014 | 4 |
12/1/2014 | 3 |
1/1/2015 | 2 |
2/1/2015 | 8 |
3/1/2015 | 7 |
4/1/2015 | 4 |
5/1/2015 | 8 |
6/1/2015 | 3 |
7/1/2015 | 5 |
8/1/2015 | 3 |
9/1/2015 | 6 |
10/1/2015 | 8 |
11/1/2015 | 9 |
12/1/2015 | 4 |
1/1/2016 | 5 |
2/1/2016 | 10 |
3/1/2016 | 4 |
4/1/2016 | 7 |
5/1/2016 | 9 |
6/1/2016 | 6 |
7/1/2016 | 6 |
8/1/2016 | 7 |
9/1/2016 | 8 |
10/1/2016 | 4 |
11/1/2016 | 10 |
12/1/2016 | 10 |
1/1/2017 | 1 |
2/1/2017 | 10 |
3/1/2017 | 9 |
Okay, I think I understand what you're asking now...
I think the second part should be:
=count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])=1'}*{'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -35))<$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>0'}>}distinct CUSTOMER_ACCOUNT_ID)
Then you would just add the above to what I gave you previously:
=count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID)
Think I got it.
RANGESUM(
count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID),
Count(if(AGGR(ONLY({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])=1'}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID)
= AGGR(ONLY({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -35))<$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])> 2'}>}
distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID),CUSTOMER_ACCOUNT_ID)))
Just saw this - this help a lot!
Thanks Nicole!!