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]

I think its difficult to understand your output here. How is 45 included in your first screenshot. Can you elaborate on the thinking behind the two questions you have here?

[tresesco]

I could not get your question1:

Didier Agonvonon wrote:

...While ignoring the minimum dates up to where the SUM of price is >=600 and <=700. The result will be :

How does your logic give the output? Could you please explain?

[didierodayo]

Hi Sunny,

I am trying this is actually a sample of a very large table. so I would like to ignore the FIRST dates for all IDs up to >=600 and <=700. So the load will ignore 125;250;300 (with their corresponding id and dates)

Secondly it is suspicious to have consecutive amount up to >=10000 . so I would like to identify any consecutive sum that equals to >=10000 which is the result is the 3rd snapshot.

I hope it is clearer now otherwise please let me know.

Thanks

[didierodayo]

Hi Tresesco,

Please check my reply to Sunny, I am only ignoring the beginning (first rows) up to the sum of >=600 and <=700.

[tresesco]

But the first value - (after 125;250;300) 400 doesn't satisfy the condition >=600 and <=700  ??

[sunny_talwar]

I guess 125;250;300 are getting ignored, but how is 400 meeting the condition? >=600 and  <= 700?

[didierodayo]

the sum of 125;250;300 is 675. so am ignoring those 3 rows before the load.

[didierodayo]

Hi Sunny, am not ignoring 400. you can see it is showing up the result in the second snapshot( result 1)

[Gysbert_Wassenaar · Accepted solution]

I think you want a cumulative sum of price:

Temp1:

LOAD

     ID,

     DATE,

     PRICE

FROM

     ...source...

     ;

Temp2:

LOAD

     ID,

     DATE,

     PRICE,

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

     If(ID = Previous(ID), Rangesum(PRICE, Peek(PRICE, -1), Peek(PRICE, -2))) as CONSEC_3DAY_SUM

RESIDENT

     Temp1

ORDER BY

     ID, DATE

     ;

DROP TABLE Temp1;

Result:

LOAD

     *,

     If(CONSEC_3DAY_SUM>=10000 or Peek(CONSEC_3DAY_SUM,-1) >= 10000 or Peek(CONSEC_3DAY_SUM,-2) >= 10000, 1, 0) as CONSEC_3DAY_FLAG

RESIDENT

     Temp2

ORDER BY

     ID, DATE desc

     ;

DROP TABLE Temp2;

Once you have this you should do the rest in chart objects imho. For example a straight table with ID and DATE as dimensions and as expression sum({<CUM_PRICE={'>700'}>}PRICE). For the second question use sum({<CONSEC_3DAY_FLAG={1}>}PRICE)