1 Reply Latest reply on Mar 28, 2016 1:46 PM by Patrick A Van Der Hyde

    Rolling CountD(A)/CountD(B) & Sum(C)/CountD(A) for the past 12 months

    dickson.kwong.1

      Hi all,

       

      I asked how to do the Rolling CountD in another post and tried to modify it in order to calucate the following. However, it doesn't seems working so far.

      https://community.tableau.com/thread/203250

       

      Wonder is it possible to generate the following graph?

      Here are the logic for Visit Frequency:

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

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

      ...

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

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

      Screenshot 2016-03-28 13.34.35.png

      Here are the logic for Av Spend per Visit:

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

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

      ...

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

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

       

      Screenshot 2016-03-28 13.35.41.png

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

       

        • 1. Re: Rolling CountD(A)/CountD(B) & Sum(C)/CountD(A) for the past 12 months
          Patrick A Van Der Hyde

          Hello Dickson,

           

          I've tried to calculate each of the desired values here in this example workbook.  The values and your images (and dates) do not match so I can not align with the examples supplied in Excel but these calculations will hopefully get you going.

           

          Two calculations of interest

           

          Window_sum(countd(member_id]),-11,0) - this will provide the sum of the distinct count of member id's for the last 11 months and then the current month.  Just change member id to Order Id for the other measure.

           

          To compare the value calculated in this calculation to one from 12 months ago utilize the lookup() command to find the value from 12 months back.  Lookup([measure],-12) and if that calculation already has the sum of the previous 12 months then you can just work with it.  I hope the examples supplied are enough to get you going.

           

          Note this workbook is in Tableau 9.3 - available for download here: - Alternate Downloads Site | Tableau Software

           

          Patrick.