1 Reply Latest reply on May 31, 2016 4:42 AM by Charlie Archer

    LOD / first order date / % of total

    Ana Yin

      Using the attached .twbx, can somebody show me how to answer this question:

       

      For customers whose 1st order date falls in January 2010, show the sum of those sales (at first order date) as a percentage of all their sales  (not sum of sales of ALL customers, but rather the history of sales of those specific customers).

       

      I'm looking for the quickest way of solving this problem, so was thinking of creating a set on 'Customer Name' using 'Condition' -> 'Formula' (just don't know what that formula is). After that, I would:

      1. Put that set to Filter, select 'True'

      2. Ctrl drag it to Rows 

      3. Sales pill to 'Text'

      4. Add quick table calculation and show Sales as percentage of total

       

      I've made a start, but as you can see, I'm solving it the 'manual' way. It would be great to know a quick formula to extract out those customers as a Set.

       

      Regards,

      Ana

        • 1. Re: LOD / first order date / % of total
          Charlie Archer

          Hi Ana,

           

          Unless i've misunderstood the question, i believe you can achieve this using an additional calculation:

           

          SUM([Sales])/SUM({FIXED [Customer ID]: SUM([Sales])})

           

          This would Sum the sales in the view according to your filter and divide this by the LOD calc (sum of the total sales for each of the customers in the view) to find the %.

           

          Thanks,

          Charlie