How to define breaks

[ecabanas]

Hi all,

I'm designing a new function and I need your help.

What I have: two tables, one is data base the other a result to sum all the invoices and group per customer and month:

what I need, I need to know per month the amount that breaks the 10%line. the 30% line and the 60% line.

What I though, create a new table with the "link" that breaks it, per month

I dont know how to do it 😞

Many thank's

[sunny_talwar · Accepted solution]

Can you check if this script gets you to what you are trying to achieve:

Customer_sales:

LOAD MonthName(created_at) &'-'&Custaccount as Link,

  MonthName(created_at) as MonthYear,

  created_at,

    Custaccount,

    total

FROM

[test_file.xls]

(biff, embedded labels, table is Sheet1$);

Join(Customer_sales)

Load MonthYear,

  Sum(total) as Gasto

Resident [Customer_sales]

Group By MonthYear;

FinalTable:

LOAD *,

  If(Peek('MonthYear') = MonthYear and Peek('FlagTemp') = FlagTemp, Null(), FlagTemp) as Flag;

LOAD *,

  If(AccuPercentage >= 0.10 and AccuPercentage <= 0.30, 1,

  If(AccuPercentage >= 0.30 and AccuPercentage <= 0.60, 2,

  If(AccuPercentage >= 0.60, 3))) as FlagTemp;

LOAD *,

  If(Peek('MonthYear') = MonthYear, RangeSum(Percentage, Peek('AccuPercentage')), Percentage) as AccuPercentage;

LOAD *,

  total/Gasto as Percentage

Resident Customer_sales

Order By MonthYear, total desc;

DROP Table Customer_sales;

Here Flag = 1 is assigned to the first time AccumulatedPercentage is greater than 10%

Flag = 2 is assigned to the first time AccumulatedPercentage is greater than 30%

Flag = 3 is assigned to the first time AccumulatedPercentage is greater than 60%

[MarcoWedel]

maybe helpful:

Recipe for an ABC Analysis

Recipe for a Pareto Analysis

regards

Marco

[ecabanas]

Hi Sunny,

First of all many thank's for your advice.

At last we did...well you did...again!!! many many thank's

Eduard

[ecabanas]

Thank you Marco 🙂

[tamilarasu]

Hi Eduard,

What software you are using to draw such nice arrows.?? Thanks.