Running total

[#1]

Hi,

Given the below data:

ID	Hits	Date
1	10	01/01/2014
1	10	01/02/2014
1	3	01/03/2014
2	3	01/01/2014
2	2	01/02/2014
3	50	01/01/2014
3	3	01/02/2014

I would like to monitor the Hits column and flag when it reaches a threshold e.g. 20.

Something like this:

ID	Hits	Date	Running_Total	Greater than 20?	First time breach?
1	10	01/01/2014	10
1	10	01/02/2014	20
1	3	01/03/2014	23	Y	Y
2	3	01/01/2014	3
2	2	01/02/2014	5
3	50	01/01/2014	50	Y	Y
3	3	01/02/2014	53	Y

I would then like to display the date that the ID breached the threshold e.g.

ID	Date
1	01/03/2014
3	01/01/2014

What is the best way to approach this?

Thanks

[jerem1234 · Accepted solution]

Hello, Please find attached. Used this script:

Data:

Load * Inline [

ID,Hits,Date

1,10,01/01/2014

1,10,01/02/2014

1,3,01/03/2014

2,3,01/01/2014

2,2,01/02/2014

3,50,01/01/2014

3,3,01/02/2014

];

Data2:

NoConcatenate Load *, if(ID=previous(ID), rangesum(Hits, peek('Running_Total')), Hits) as Running_Total,

if(if(ID=previous(ID), rangesum(Hits, peek('Running_Total')), Hits)>20, 'Y') as [Greater than 20?],

if(if(ID=previous(ID), rangesum(Hits, peek('Running_Total')), Hits)>20 and peek('Greater than 20?')<>'Y', 'Y') as [First time breach?]

Resident Data

Order by ID asc, Date asc;

Drop Table Data;

Then for a table for the date, used set analysis in a straight table with ID as dimension and the expression:

only({<[First time breach?]={'Y'}>}Date)

Hope this helps!

[MK_QSL]

T1:

Load

  ID,

  Hits,

  Date(Date#(Date,'MM/DD/YYYY')) as Date

Inline

[

  ID, Hits, Date

  1, 10, 01/01/2014

  1, 10, 01/02/2014

  1, 3, 01/03/2014

  2, 3, 01/01/2014

  2, 2, 01/02/2014

  3, 50, 01/01/2014

  3, 3, 01/02/2014

];

T2:

Load

  *,

  IF(CumulativeHits >= 20, 1) as CumulativeHitsFlag;

Load

  ID,

  Hits,

  Date,

  If(ID = Previous(ID), RangeSum(Peek('CumulativeHits'),Hits),Hits) as CumulativeHits

Resident T1

Order By ID, Date;

Left Join

Load

  ID,

  Date(Min(Date)) as MinDate

Resident T2 Where CumulativeHitsFlag = 1

Group By ID;

Drop Table T1;

Drop Fields CumulativeHitsFlag, CumulativeHits;

[datanibbler]

Hi PortoLad,

use the PEEK() function:

- First sort your table by that ID so you have all records with the same ID in one group, then use sth. like this

>> IF(ID = PEEK(ID, -1), PEEK(Hits, -1) + Hits, Hits) as running_total <<

You just have to take care of the very first record since the PEEK() fct. won't work there,

so you have to build in an additional IF() statement for a line_ID (Recno())

HTH

Best regards,

DataNibbler

[#5]

You could use a few preceding load statements to sort this out:

Sample:

Load *,

If(ID = Peek('ID'), If(Peek('TotalHits') <= 20 and TotalHits > 20,'Y','N'),[>20Flag]) as FirstBreach,

Peek('TotalHits') as Tester

;

Load *,

If(TotalHits > 20,'Y','N') as [>20Flag]

;

Load *,

If(Peek('ID') = ID, Peek('TotalHits'),0) + Hits as TotalHits

;

Load * Inline [

ID, Hits, Date

1, 10, 41640

1, 10, 41641

1, 3, 41642

2, 3, 41640

2, 2, 41641

3, 50, 41640

3, 3, 41641

]

;

[#6]

I found it helpful.

[#7]

That has been very helpful - thanks!

[#8]

THANKS FOR SHARING, THIS HAS REALLY HELPED ME !!!

[#9]

thanks to Jeremiah, Manish, Friedrich, Tyler and Janaki for their help