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

# Calculation Problems - Distance From Average

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

Hi Marc

Find my approach as reference below and stored in attached workbook version 2018.2 located in the original thread 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

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?