topic Re: Compare Multiple years list in QlikView

Compare Multiple years list

Tue, 21 Mar 2017 22:00:48 GMT

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

Re: Compare Multiple years list

Nicole-Smith — Wed, 22 Mar 2017 17:18:47 GMT

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.

Re: Compare Multiple years list

Wed, 22 Mar 2017 19:43:37 GMT

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

Re: Compare Multiple years list

Nicole-Smith — Wed, 22 Mar 2017 20:10:12 GMT

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)

Re: Compare Multiple years list

Wed, 22 Mar 2017 20:19:07 GMT

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

Re: Compare Multiple years list

Wed, 22 Mar 2017 20:20:05 GMT

Just saw this - this help a lot!

Thanks Nicole!!