2 Replies Latest reply on Nov 17, 2018 9:05 AM by Marc Monteys

    Calculation Problems - Distance From Average

    Marc Monteys

      hi,

       

      i'd like to calculate the customers with unusual number of orders of a product in relation to the other customers.

      so i need a reference,  In my case the average of %

      If I do it with superstore source data everything goes well but if I do it with EXCEL (even with a excel extraction) i can't do the calculations

      anyone knows why?

       

      see the example attached

        • 1. Re: Calculation Problems - Distance From Average
          Norbert Maijoor

          Hi Marc

           

          Find my approach as reference below and stored in attached workbook version 2018.2 located in the original threadsizegrate.jpg

           

          1. M1. Cound Ord ID: countd([Order Id.])

           

          2. M2. Qty Orders per Customer:

          if size()<>1 then sum({fixed [Customer Name]:([M1. Cound Ord ID])})

          elseif size()=1 then sum({fixed :([M1. Cound Ord ID])})

          END

           

          3. M3. % count:

          if size()<>1 then [M1. Countd Ord ID]/[M2. Qty Orders per Customer]

          elseif size()=1 then [M1. Countd Ord ID]/[M2. Qty Orders per Customer]

          END

           

          Hope it helps,

           

          Regards,

          Norbert

          • 2. Re: Calculation Problems - Distance From Average
            Marc Monteys

            Hi Norbert,

            thank you for the help.

             

            What I'm trying to get 5 calculations:

             

            • C1 : (works fine in both data sources) Qty Ord : | 1 | Quantity of orders. Easy in this case coz 1 row = 1 order so calculation is just a duplicate of "number of rows"
            • C2 : (works fine in both data sources) Total Ord. Of Customer : | {FIXED [Customer Name]:SUM([Qty Ord.])} | Total orders af a customer no matter what product it is.
            • C3 : (works fine in both data sources) % Qty / Total Customer : | SUM([Qty Ord.])/SUM([Total Ord. Of Cust.]) | % of what the qty of orders (C1) represents on total of orders of that client (C2) , that is C1/C2
            • C4 : (works fine just in superstore!!!!! ) Avg of Cust. % : | {FIXED [Product Name]:AVG([Qty Ord.]/[Total Ord. Of Cust.])} | The average of all values found in previous calculation C3 by product. That is:
              • (Superstore) 1.7 Cubic Foot Compact "Cube" Office Refrigerators = 3,72
              • (Superstore) Avanti 1.7 Cu. Ft. Refrigerator = 7,07
              • (Excel) Product1 = 49,44
              • (Excel) Product2 = 62,92
            • C5: Distance From Avg: | SUM([Avg Of Cust. %])-[% Qty / Total Cust.] | Distance beetwen C3 from C4 to show which customers have an unusual qty of order of a particular product versus the rest of the customers.

             

            The point is that all my calculation work fine with superstore data source but C4 doesn't works fine with Excel data source (now is 16,39 but it should be 49,44)

             

            Any idea why?