Solved: Re: How to define breaks - Qlik Community

ecabanas · ‎2016-03-16

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 · ‎2016-03-16

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%

View solution in original post

sunny_talwar · ‎2016-03-16

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

Not sure I understand your requirement here. Would you be able to elaborate a little?

ecabanas · ‎2016-03-16

Hi Sunny,

You are right, so sorry, see what I need in the excel attached. For every month, we need the total that breaks the 10%,30% and 60% accum revenues.

Better explained in the excel 😉

Many thank's

sunny_talwar · ‎2016-03-16

In your example the total fields seems to be unsorted. the accumulated 10%, 30% values are correctly calculated? no sorting on created date or Custaccount needed? Randomly sorted data might show a different 10% value if something changes in your data which might change the sorting of the datasource. Is that acceptable?

ecabanas · ‎2016-03-16

Hi Sunny,

No, no, the data is sorted by, MonthYear and the total desc

sunny_talwar · ‎2016-03-16

Right, but no sorting within a single MonthYear?

sunny_talwar · ‎2016-03-16

Look at the screenshot I have posted above

ecabanas · ‎2016-03-16

Hi Sunny,

The excel is sorted by MonthYear Asc and the total Desc. See the screenshot below

sunny_talwar · ‎2016-03-16

I got it. My bad

ecabanas · ‎2016-03-16

don't worry

Many thank's Sunny