Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

How to define breaks

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:2016-03-16_11h02_00.png

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

1 Solution

Accepted Solutions
sunny_talwar

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

14 Replies
sunny_talwar

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
Creator II
Creator II
Author

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

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?

Capture.PNG

ecabanas
Creator II
Creator II
Author

Hi Sunny,

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

sunny_talwar

Right, but no sorting within a single MonthYear?

sunny_talwar

Look at the screenshot I have posted above

ecabanas
Creator II
Creator II
Author

Hi Sunny,

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

2016-03-16_11h55_07.png

sunny_talwar

I got it. My bad

ecabanas
Creator II
Creator II
Author

don't worry

Many thank's Sunny