2 Replies Latest reply on Mar 26, 2016 1:57 AM by dickson.kwong.1

# Rolling CountD for the past 12 months

Hi all,

Wonder is it possible to generate the following graph?

Here is the logic:

@Apr blue bar - COUNTD(Member.ID) for the period of (2014 May to this year 2015 Apr)

@May blue bar - COUNTD(Member.ID) for the period of (2014 Jun to this year 2015 May)

...

@Apr red line - % change of [COUNTD(Member.ID) for the period of (2013 May to this year 2014 Apr)] and [COUNTD(Member.ID) for the period of (2014 May to this year 2015 Apr)]

@May red line - % change of [COUNTD(Member.ID) for the period of (2013 Jun to this year 2014 May)] and [COUNTD(Member.ID) for the period of (2014 Jun to this year 2015 May)]

Only two column in the datafile, Order.Date & Member.ID

Any suggestions how to solve this question?

• ###### 1. Re: Rolling CountD for the past 12 months

Hi.

Mmm... It's not easy, but it is possible.

(If anyone knows more easy way, please let me know!)

1. Create the table of year, month and id like below.

2. Create some calculated fields.

Is.Exist.L12M

```WINDOW_MIN(SUM([Number of Records]),-11,0)
```

- from 11 months ago (e.g. Mar 2014) to current(0) month (e.g. Apr 2015), return min of SUM([Number of Records]) (i.e. if the particular member of record is exist return 1, else return null)

Count(L12M)

```WINDOW_SUM([Is.Exist.L12M])
```

- sum of [Is.Exist.L12M] (i.e. COUNTD([Member.ID]) because [Is.Exist.L12M] returns 1 if the particular member of record is exist in the latest 12 months.)

3. Put [Count(L12M)] into Rows and Edit Table Calculation as below.

- for the field [Is.Exist.L12M], Compute using [Order.Date] At the level Month

for that WIMDOW_MIN(...,-11,0) indicates from 11 months ago to current month

- for the field [Count(L12M)], Compute using [Member.ID]

for that WINDOW_SUM(...) indicates sum of the particular member's existence

So far, you will get the view like this.

Now you can get the count(distinct) of member for the period(L12M).

But it repeats for the count of member id... So continue some more steps to complete the view.

4. Create another calculated field and edit table calculation like...

Index

```INDEX()
```

and put it into Rows.

5. add it to filter and select only 1 like...

and uncheck 'Show Header' of [Member.ID] and [Index].

6. Create [%change] field and edit table calculation like...

%change

```([Count(L12M)]-LOOKUP([Count(L12M)],-1))
/LOOKUP([Count(L12M)],-1)
```

- for that LOOKUP(...,-1) indicates the last year.

and put it into Rows.

7. Finally, use dual axis and edit some settings (color etc.), then you get the view as below!

If you feel any step above is hard, please tell me!

v.9.2 attached.

Thanks.

• ###### 2. Re: Rolling CountD for the past 12 months

Appreciate your help. This is exactly what I am looking for.

Didn't think of index can do the trick.

(it is a bit slow on my end as I got 13mi row of record but this is OT)

Dickson