5 Replies Latest reply: Mar 22, 2017 4:20 PM by Azmina Virani

# Compare Multiple years list

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):

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)

CUSTOMER_ACCOUNT_ID = {"=Count(CUSTOMER_ACCOUNT_ID) = 1"}>}distinct CUSTOMER_ACCOUNT_ID)

This is to calculate all account with 1 transaction count:

count  (AGGR(

CUSTOMER_ACCOUNT_ID = {"=Count(CUSTOMER_ACCOUNT_ID) = 1"}>}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
• ###### Re: Compare Multiple years list

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 )

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)

I've also attached an example file.  Let me know if it fixes the issue.

• ###### Re: Compare Multiple years list

Almost -

I still am not able to get the last part.

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
• ###### Re: Compare Multiple years list

Okay, I think I understand what you're asking now...

I think the second part should be:

Then you would just add the above to what I gave you previously:

• ###### Re: Compare Multiple years list

Just saw this - this help a lot!

Thanks Nicole!!

• ###### Re: Compare Multiple years list

Think I got it.

RANGESUM(