Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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%
Hi Sunny,
First of all many thank's for your advice.
At last we did...well you did...again!!! many many thank's
Eduard
Thank you Marco 🙂
Hi Eduard,
What software you are using to draw such nice arrows.?? Thanks.