5 Replies Latest reply on Jul 8, 2017 9:51 AM by Okechukwu Ossai

    Viz Challenge - Calculate the customer number who also bought in previous month in a table.

    Michael Gao

      Hi Guru,

       

      I got a viz challenge from customer.

       

      Customers bought this month also might have bought in previous month, for example:

       

      201702  customer count: 100, in those 100 there're 39 who also bought in 201701;

      201703 customer count: 123, in those 123 there're 80 who also bought in 201702;

      201704 customer count: 230, in those 230 there're 90 who also boght in 201704.

      and so on......

       

      Customer need a simple table show in each month, how many customer bought in this month, and in those customers how many customers also bought in previous month.

       

      To count the customers bought in this month is easy, but how to calculate the number of customers who also bought in previous month?

       

      Thanks and BR,

       

      Michael Gao

        • 1. Re: Viz Challenge - Calculate the customer number who also bought in previous month in a table.
          Okechukwu Ossai

          Hi Michael,

           

          This can be done with table calculations - Lookup(), First(), Window functions etc. However, I opted for a combination of parameters, LOD expressions and calculations using the Sample Superstore data. The calculated fields have been broken down into smaller chunks but you may skip or combine them as you wish.

           

           

          Step 1: Create parameter [Month]

          This is a string list of calendar months

           

          Step 2: Create parameter [Year]

          This is an integer formatted as slider. I restricted the parameter range between 2012 and 2016, which is the sample data limit. Feel free to modify this to suit your data range.

           

          Step 3: Create calculated field [Current Month]

          {FIXED [Customer Name], [Order Date]: MAX(DATE(DATEPARSE("dd/MMM/yyyy", "01"+"/"+[Month]+"/"+STR([Year]))))}

          The formula converts the user selected month into a date and then fixes this date for every record in the database. This is required to do a row level comparison with [Order Date].

           

          Step 4: Create calculated field [Previous Month]

          {FIXED [Customer Name], [Order Date]: MAX(DATE(DATEADD('month', -1, DATEPARSE("dd/MMM/yyyy", "01"+"/"+[Month]+"/"+STR([Year])))))}

          The formula calculates previous month based on user month selection. The calculated date is then fixed for every record using an LOD expression. Again, this will be used for a row level date comparison with [Order Date].

           

          Step 5: Create calculated field [Valid Date]

          IF DATETRUNC('month', [Order Date]) >= [Previous Month] AND DATETRUNC('month', [Order Date]) <= [Current Month]

          THEN DATE(DATETRUNC('month', [Order Date])) END

          We are only interested in Customers who both this month and the previous month. So, this formula will return null except for previous and current months based on user selection. If a customer has only null valid dates, then they didn't buy this month or last month. If count of valid date is 1 for a customer, then they bought either this month or previous month. A customer who bought this month and last month will have count of valid date as 2.

           

          Step 6: Create calculated field [Bought This Month]

          {FIXED DATETRUNC('month', [Order Date]): COUNTD([Customer Name])}

          This formula calculates the number of customers who made a purchase in any calendar month.

           

          Step 7: Create calculated field [Also Bought Last Month]

          {FIXED DATETRUNC('month', [Order Date]): COUNTD(IF {FIXED [Customer Name]: COUNTD([Valid Date])} = 2 THEN [Customer Name] END)}

          Extending the valid date count explanation in Step 5, this formula will count the number of customers who bought this month and also last month.

           

          Step 8: Create calculated field [Date]

          DATE(DATEPARSE("dd/MMM/yyyy", "01"+"/"+[Month]+"/"+STR([Year])))

          This converts the user selection into a date. This is optional. I've only used it for a neat display on the worksheet when formatted as mmm yyyy.

           

          Step 9: Create calculated field [Date Filter]

          DATE(DATETRUNC('month', [Order Date])) = DATE(DATEPARSE("dd/MMM/yyyy", "01"+"/"+[Month]+"/"+STR([Year])))

          This is a boolean expression. Put it on the filter shelf and set to True. This will exclude all dates except the user selected month.

           

          Hope this helps.

          Ossai

          • 2. Re: Viz Challenge - Calculate the customer number who also bought in previous month in a table.
            Michael Gao

            Hi Okechukwu,

             

            That's a brilliant method! Many thanks for your reply and detailed steps!

             

            However, the result with LOD can only display a single month (Year+Month) value, the original requirement is generate a table with all months' value in the table, and I did it via data blending + self join method.

             

            Pls refer to this post (I posted the same topic in Beijing community) : Viz Challenge - Customers who bought in last month and this month

             

             

            BR,

             

            Michael Gao

            • 3. Re: Viz Challenge - Calculate the customer number who also bought in previous month in a table.
              Okechukwu Ossai

              Hi Michael,

               

              Well done for achieving an alternative solution using self-join and blending.

               

              My LOD solution meets the original requirement. It can display results for all months and years. I didn't add charts and tables to my previous answer, since that seems to be the easy part. One thing to bear in mind is that the solution with LOD calculations may perform better than blending and self-join, especially when you have several million rows of data.

               

              Below is a visualization created using the LOD results. See attached workbook in version 10.0

               

              Hope this helps

              Ossai

               

              • 4. Re: Viz Challenge - Calculate the customer number who also bought in previous month in a table.
                Michael Gao

                Thank you Okechukwu!

                 

                I'll study your method and propose it to our customer:)

                 

                BR,

                 

                Michael