Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

=(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 DateCUSTOMER_ACCOUNT_ID
1/1/201410
2/1/20146
3/1/20144
4/1/20147
5/1/20144
6/1/20144
7/1/20144
8/1/20147
9/1/20142
10/1/20142
11/1/20144
12/1/20143
1/1/20152
2/1/20158
3/1/20157
4/1/20154
5/1/20158
6/1/20153
7/1/20155
8/1/20153
9/1/20156
10/1/20158
11/1/20159
12/1/20154
1/1/20167
2/1/201610
3/1/20164
4/1/20167
5/1/20169
6/1/20166
7/1/20166
8/1/20167
9/1/20168
10/1/20164
11/1/201610
12/1/201610
1/1/20171
2/1/201710
3/1/20179
1 Solution

Accepted Solutions

Re: Compare Multiple years list

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)

5 Replies

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 )

=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.

Not applicable

Re: Compare Multiple years list

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/201410
2/1/20146
3/1/20144
4/1/20147
5/1/20144
6/1/20144
7/1/20144
8/1/20147
9/1/20142
10/1/20142
11/1/20144
12/1/20143
1/1/20152
2/1/20158
3/1/20157
4/1/20154
5/1/20158
6/1/20153
7/1/20155
8/1/20153
9/1/20156
10/1/20158
11/1/20159
12/1/20154
1/1/20165
2/1/201610
3/1/20164
4/1/20167
5/1/20169
6/1/20166
7/1/20166
8/1/20167
9/1/20168
10/1/20164
11/1/201610
12/1/201610
1/1/20171
2/1/201710
3/1/20179

Re: Compare Multiple years list

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)

Not applicable

Re: Compare Multiple years list

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

Not applicable

Re: Compare Multiple years list

Just saw this - this help a lot!

Thanks Nicole!!

Community Browser