Complexe sum script

[didierodayo]

Hi Experts,

I am faced with a small challenge. I have the table below which holds ID;DATE; PRICE.

ID	DATE	PRICE
00234	15/02/2016	125
00234	21/03/2016	250
00234	22/03/2016	300
00234	28/03/2016	400
00234	17/04/2016	900
00234	25/04/2016	3500
00234	05/05/2016	4000
00234	15/05/2016	6000
00234	21/05/2016	750
00234	06/06/2016	200
00234	20/06/2016	120
00234	01/07/2016	45

question1: while loading the table I would like to return only the id;date;price While ignoring the minimum dates up to where the SUM of price is >=600 and <=700. The result will be :

Question2: From the original I would like to load only the id;date;price where the Sum of Price for any consecutive 3 dates is >=10000.

the expected result will be.

I would appreciate any help.

Regards

[sunny_talwar]

If for some reason your first 3 data points were like this:

125;250;400 totaling to 775, nothing would have been ignored?

[didierodayo]

Yes Sunny that is correct the requirement is such that if it is above 700 then it should be ignored.

[didierodayo]

Hi GD,

I will try this and confirm . at first look I think it could fix the second question. for the first question we are dealing only with the beginning row where the total is >=600 and <=700.  So if any other rows on the table were to be equal to 600 they won't be ignored.

Thanks

[sunny_talwar]

This should meet your first requirement:

Table:

LOAD ID,

    DATE,

    PRICE

FROM

[https://community.qlik.com/thread/225056]

(html, codepage is 1252, embedded labels, table is @1);

TEMP_TABLE:

LOAD ID,

  DATE,

  PRICE,

  If(ID = Previous(ID), RangeSum(Peek('CUM_PRICE'), PRICE), PRICE) as CUM_PRICE,

  If(If(ID = Previous(ID), RangeSum(Peek('CUM_PRICE'), PRICE), PRICE) >= 600 and

    If(ID = Previous(ID), RangeSum(Peek('CUM_PRICE'), PRICE), PRICE) <= 700, 1, 0) as FLAG

Resident Table

Order By ID, DATE;

TEMP2_TABLE:

LOAD *,

  If(ID = Previous(ID), If(FLAG = 1, FLAG, Peek('NEW_FLAG')), FLAG) as NEW_FLAG

Resident TEMP_TABLE

Order By ID, DATE desc;

FINAL_TABLE:

NoConcatenate

LOAD *

Resident TEMP2_TABLE

Where NEW_FLAG = 0;

DROP Table Table, TEMP_TABLE, TEMP2_TABLE;

[tresesco]

Perhaps a peek() is missing:

If(ID = Previous(ID), Rangesum(PRICE, peek(CUM_PRICE)) , PRICE) as CUM_PRICE,

[Gysbert_Wassenaar]

Thanks! Fixed above.