Customer with Activity in past 12 months

[mcsshg2011]

 

Hi,

I need to count customer that has Activity in the past 12 months based on the period selected and I will need to show it in a table for 12 rolling months.

Above table indicate when the customer has transactions.

In Qlik Sense, I have the selection for Fiscal Period.

So, if I choose Fiscal Period 2017-12, the table will give me 12 months from 2017-01 to 2017-12

Active customer count should show as follows:

2017-01 = 1

Each period from 2017-02 up to 2017-12  = 2

However, if I choose Fiscal Period 2017-11, it will show

2016-12 = 1

2017-01 = 2

Each period from 2017-02 up to 2017-11 = 3

The follow set analysis does not work as it will give me what is shown on the table.

=Count({<FinYrPeriod2={">=$(vPeriod12MonthsBack)<=$(vPeriodSelected)"},TransactionType={"Last Activity"} ,FiscalYear=,FiscalPeriod=>} CustomerNo)

The following does not work either:

=Count({<TransactionDate={">=Min(TransactionDate))<=Max(TransactionDate)"},TransactionType={"Last Activity"} ,FiscalYear=,FiscalPeriod=>} CustomerNo)

For 2017-12 - rolling 12 months

Min(TransactionDate) = 28/6/2016 and Max(TransactionDate)=30/6/2017

For 2017-11 - rolling 12 months

Min(TransactionDate) = 1/5/2016 and Max(TransactionDate)=31/5/2017

Using expression to find Min(TransactionDate) and Max(TransactionDate) works.  But, it does not seem to work when I put it in Set Analysis as it just return zero.

Any help is appreciated.

[pradosh_thakur]

DID YOU TRY USING RANGESUM before your expression

something similar to this

rangesum(Count({<FinYrPeriod2={">=$(vPeriod12MonthsBack)<=$(vPeriodSelected)"},TransactionType={"Last Activity"} ,FiscalYear=,FiscalPeriod=>} CustomerNo))

share a sample if possible.

regards

Pradosh

[vikasdesai]

Hello ,

Please use following Expression

Count({<DateField = {">=$(=MonthStart(Max(DateField),-11))<=$(=MonthEnd(Max(DateField)))"},,Year=,Month=>}CustomerNo))

Regards

Vikas

[mcsshg2011]

Hi,

Thanks for responding, but I can't get them to work with both suggestion.  Let me provide more details on what I try to achieve.

User can select 1 Fiscal Period only and it will display the rolling 12 months information in pivot.

EG: If 2017-12 is selected, it will show 2017-01 to 2017-12.  When 2017-10 is selected, it will show 2016-11 to 2017-12.

In each period, it counts the customer that have activity in the past 12 months in the Period dimensions.

Eg: In 2017-01, a customer is considered an active customer if he has activity between 2016-02 to 2017-01.  So, For 2017-01, it counts the number of active customers. between 2016-02 to 2017-01.

[vikasdesai]

Hello

I have given u Only One Suggestion

The Expression I have given is One only a one expression so use it as whole expresssion in ur syntax then u will get desire results

Regards

Vikas