3 Replies Latest reply on Jun 7, 2018 6:59 AM by Bryce Larsen

    Need help with a table calc.

    Vikram Gokhale

      First off sincere apologies for not posting twbx file. Will try and get to it asap and post it. But in the mean time this is what I have.

       

      My Worksheet has a big table with about 20 cols. First few columns are as below...

      There are also two parameters for Year and Month.

       

      Param1: Year(2015, 2016,2017,2018)          Param2: Abbrv. Month (JAN, FEB, MAR....DEC)

       

      Product Group
      Product
      Total Trans. Volume
      New CustomersDefector
      Product Group 1Product 1321578662??
      Product 25650??
      Product 3385315??
      Product Group 2Product 4100012??
      Product 56884584??
      Product 654680??
      Product Group 3Product 7327180??
      Product 81256469

      There exists a hierarchical relationship between Product Group and Product. So Product Group can be expanded and collapsed in the grid using the '+' sign.

       

      I need guidance on finding value for the Defector Count (last column).

      Definition of a Defector : If the volume for currently Selected Year & Month for a customer is 0. But that customer has had volume>100 at some point of time in past then the customer is classified as a defector.

       

      I am able to do it if I list the individual Customers, Date and Volume figures and use Lookup fn. to figure out the numbers. But in table I don't have that level of granularity.

       

      Any thoughts ?

        • 1. Re: Need help with a table calc.
          Bryce Larsen

          Hi Vikram Gokhale,

          I took a stab at this randomly throughout the day while waiting for other things to run because it seemed a fun challenge. I think I was able to come up with the desired result using several LOD expressions. To make things easier, I ended up consolidating to just a few.

           

          I used Sample - Superstore and in my example Defector: a customer with no sales during month selected but sales in a previous month exceeding $50.

           

          Essentially I had to make two separate Defector Flags - one for Category and one for Product Name (you would replace Category with Product Group here). They are identical except you include [Product Name] for the second Flag:

           

          Then you simply make a new Measure that counts the customers. However, you need to know which to use since it's hierarchical:

           

          Then I was able to build the final table looking at the month selected, the number of customers per category this month, total sales, and number of calculated defectors:

          I converted customer count and sales to strings so they'd display as an empty string rather than writing out "Null" when you drilldown to Product Name:

           

          Hope this helps!

           

          Best,

          Bryce

          1 of 1 people found this helpful
          • 2. Re: Need help with a table calc.
            Vikram Gokhale

            Thanks for your inputs Bryce. Will definitely look into this and see if it works... Thanks again..

            • 3. Re: Need help with a table calc.
              Bryce Larsen

              Fingers crossed...!