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

    Rolling CountD for the past 12 months

    dickson.kwong.1

      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

      Screenshot 2016-03-21 18.40.05.png

      Any suggestions how to solve this question?

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

          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.

          step01.png

           

          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

          step02.png

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

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

          step03.png

          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.

          step04.png

           

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

          Index

          INDEX()
          

          step05.png

          and put it into Rows.

          step06.png

           

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

          step07.png

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

          step08.png

           

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

          %change

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

          step09.png

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

          and put it into Rows.

          step10.png

           

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

          step11.png

           

          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
            dickson.kwong.1

            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