Minggu, 01 Mei 2016

[MS_AccessPros] calculating net change

 

I have data that I want to analyze in terms of daily net change. Data reflecting the current volume is collected on a daily basis from approx. 75 locations. The data provider is unable to collect daily changes but needs this value computed by subtracting the previous reading from the last reading. Some locations have frequently changing daily levels while other have only occasional levels.

 

I am able to produce a short-term history (7 days) using a crosstab to flatten the date. I use the crosstab as a subquery in which each column is subtracted from the column that represents its predecessor.

 

The situation is similar to electrical power data where meter readings are periodically collected and consumption is calculated by subtracting the previous reading from the current reading. My data is slightly different because the values may indicate decreases as well as increases but this shouldn’t affect the calculation.

 

Setting up a crosstab to calculate over the long term (as opposed to just the past 7 days) is not really practical. So I am looking for an efficient way to calculate periodic changes for a large number of periods.

 

Sample Date:

 

SiteHistoryID

SiteHistoryAsOfDate

SiteHistoryCount

SiteID

3455

17-Apr-16

67035

696

3461

16-Apr-16

67034

696

3463

17-Apr-16

40970

697

3465

17-Apr-16

39186

698

3466

17-Apr-16

33243

699

3467

17-Apr-16

32211

700

3473

16-Apr-16

32060

700

3475

15-Apr-16

27102

701

3481

16-Apr-16

28282

701

3482

17-Apr-16

28000

701

3484

17-Apr-16

25075

702

3487

17-Apr-16

24895

703

3492

16-Apr-16

20506

706

3497

16-Apr-16

20474

707

3519

16-Apr-16

15858

715

3531

16-Apr-16

13706

720

3543

16-Apr-16

10320

728

3560

16-Apr-16

9146

733

3570

16-Apr-16

9270

739

3580

16-Apr-16

7609

742

3629

16-Apr-16

6504

769

3766

16-Oct-12

16417

773

 

Glenn

__._,_.___

Posted by: "Glenn Lloyd" <argeedblu@gmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

Upgrade your account with the latest Yahoo Mail app
Get organized with the fast and easy-to-use Yahoo Mail app. Upgrade today!


.

__,_._,___

Tidak ada komentar:

Poskan Komentar