topic Re: How to define breaks in QlikView

How to define breaks

ecabanas — Wed, 16 Mar 2016 10:05:14 GMT

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

Re: How to define breaks

sunny_talwar — Wed, 16 Mar 2016 10:10:21 GMT

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?

Re: How to define breaks

ecabanas — Wed, 16 Mar 2016 10:38:50 GMT

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

Re: How to define breaks

sunny_talwar — Wed, 16 Mar 2016 10:44:09 GMT

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?

Re: How to define breaks

ecabanas — Wed, 16 Mar 2016 10:46:31 GMT

Hi Sunny,

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

Re: How to define breaks

sunny_talwar — Wed, 16 Mar 2016 10:47:52 GMT

Right, but no sorting within a single MonthYear?

Re: How to define breaks

sunny_talwar — Wed, 16 Mar 2016 10:48:09 GMT

Look at the screenshot I have posted above

Re: How to define breaks

ecabanas — Wed, 16 Mar 2016 10:57:13 GMT

Hi Sunny,

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

Re: How to define breaks

sunny_talwar — Wed, 16 Mar 2016 10:58:56 GMT

I got it. My bad

Re: How to define breaks

ecabanas — Wed, 16 Mar 2016 11:05:21 GMT

don't worry

Many thank's Sunny

Re: How to define breaks

sunny_talwar — Wed, 16 Mar 2016 11:18:50 GMT

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%

Re: How to define breaks

MarcoWedel — Wed, 16 Mar 2016 21:42:51 GMT

maybe helpful:

Recipe for an ABC Analysis

Recipe for a Pareto Analysis

regards

Marco

Re: How to define breaks

ecabanas — Thu, 17 Mar 2016 08:30:25 GMT

Hi Sunny,

First of all many thank's for your advice.

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

Eduard

Re: How to define breaks

ecabanas — Thu, 17 Mar 2016 08:43:52 GMT

Thank you Marco 🙂

Re: How to define breaks

tamilarasu — Thu, 17 Mar 2016 09:19:25 GMT

Hi Eduard,

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