2 Replies Latest reply on Apr 23, 2017 5:07 AM by shay yefet

    Formula questions

    shay yefet

      Hi,

       

      Attached is a workbook :

      1.The list of customers that includes the total sales to the customer, I need to know the months since the first purchase (in the format "NN months"), in addition to the number of months they were active (purchases made) and average sales per month.

      2.  I need to add a column to the list, the type of customers who made purchases in less than 10% of the months since the first purchase

      3. top selling product for each product sub-category, including sales, profit, % profit, and the percentage of the total sales for that sub-category

       

       

      Thanks ,

        • 1. Re: Formula questions
          Simon Runc

          hi Shay,

           

          So you've got a lot of requests in there!...so here goes

           

          I've used FIXED LoDs to generate the flags, as this means they work regardless of the VizLoD...

           

          First I created a MAX  date for the Data (as the data goes up to Dec 14, I didn't want the count of months since last purchase to be to today)

          [Data To]

          {MAX([Order Date])}

           

          Next we need to pick up the First Order Date for each customer

          [First Order Per Customer]

          {FIXED [Customer Name]: MIN([Order Date])}

           

          and we can then use these 2 fields to calculated the number of months

          [Months Since First Purchase]

          DATEDIFF('month',[First Order Per Customer], [Data To])

           

          and I formatted it to return in the "NN months" format

           

          So next we want to know how many months they actually had purchases...

          [Months of Purchases Per Customer]

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

           

          and then we can create a flag to determine if this is > or < 10% of the months since first purchase...

          [Purchase in 10% of Months Flag]

          [Months of Purchases Per Customer]/[Months Since First Purchase]>0.1

           

          Although this is a boolean, I used the Alias to return a "human readable" output...

           

          The "CustomerTable" tab shows this, and some of the values our calculations....so you can see what's going on.

           

          Next is Products!

           

          First I create a flag for the top selling product, per subcat

          [Top Selling Product per SubCategory Flag]

          {FIXED [Product]: SUM([Sales])}

          =

          {FIXED [Sub Category]: MAX(

          {FIXED [Product]: SUM([Sales])})

          }

           

          So what this does is return the Total Product Sales for each product. Finds which is the Max value, and compares that to each product...so only the top one from each subcategory get's flagged.

           

          The "ProductTable_HowItWorks" tab shows how it works.

           

          Next we need to return that name to all rows per subcat

          [Top Selling Product Name]

          {FIXED [Sub Category]: MAX(IIF([Top Selling Product per SubCategory Flag],[Product],NULL))}

           

          Our Top Selling product Sales, Profit and Profit % are all in the form (I've shown the one for Sales here)

          [Top Selling Product - Sales]

          IIF([Top Selling Product per SubCategory Flag],[Sales],NULL)

           

          and finally the Share of SubCat Sales for the Top Product is done as per the below

          [Top Selling Product - Share of Sales]

          SUM([Top Selling Product - Sales])

          /

          SUM([Sales])

           

          and we're done!...so quite a bit going on here, but hope this helps and makes sense.

          • 2. Re: Formula questions
            shay yefet

            Hi Simon,

             

            Thanks for your great help

            An excellent and very clear answer