Let's Get Social!
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [Solved]Getting Prev Year Count
 Go New Search Notify Tools Reply
 [Solved]Getting Prev Year Count
Platinum Member
 posted January 31, 2019 05:12 PM
Hi,

I have the below data set with first two columns.
```
Year Product   Curr Count  Prev Count  Diff

2011	1	50		0	50
2	40		0	40
3	500		0	500
2012	1	20		50	-30
2	200		40	160
3	600		500	100
2013	1	100		20	80
2	50		200	-150
3	700		600	100
2014	1	400		100	300
2	200		50	150
3	850		700	150
2015	1	150		400	-250
2	300		200	100
3	1050		850    	200
```

I have Year and Curr Count columns and i am trying to calculate the rest of the column.
It looks very simple and i have done something similar using
`LAST`
command.But now i am not able to bring the Prev Count column.Once i have Prev Count i can calculate diff.But i am stuck in logic to calculate Prev Count.

Any help would be appreciated.

This message has been edited. Last edited by: srajeevan,

WF8202M,Windows 7,8,10
HTM,PDF,EXCEL

 Posts: 208 | Location: MI | Registered: September 13, 2017 IP
Virtuoso
 posted February 01, 2019 04:17 AM Hide Post
You can achieve the desired result by doing it through an intermediate file.
First extract the data, sort it by product and year - which will allow your calculations to be performed correctly. Then hold the result and report off that hold file. The simplified example for this would be something like:
```TABLE FILE xyzzy
SUM   CNTR
COMPUTE PCNTR/I6 = IF PROD NE LAST PROD THEN 0 ELSE LAST CNTR;
COMPUTE DIFF/I6  = CNTR - PCNTR;
BY PROD
BY YEAR
ON TABLE HOLD
END

TABLE FILE HOLD
PRINT CNTR   AS 'Curr Count'
PCNTR  AS 'Prev Count'
DIFF   AS 'Diff'
BY    YEAR   AS 'Year'
BY    PROD   AS 'Product'
END
```

Hope this helps...

GamP

 - Using AS 8.2.01 on Windows 7 - IE11. in Focus since 1988

 Posts: 1960 | Location: Netherlands | Registered: September 25, 2007 IP
Master
 posted February 01, 2019 08:42 AM Hide Post
Have you looked at the PARTITION_AGGR and PARTITION_REF functions?

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

 Posts: 942 | Location: Oklahoma City | Registered: October 27, 2006 IP
Platinum Member
 posted February 04, 2019 09:21 AM Hide Post
@GamP : I was able to use your solution.Thanks.
@jgelona : I will try to use the functions you mentioned and see if it can help.Thanks.

WF8202M,Windows 7,8,10
HTM,PDF,EXCEL

 Posts: 208 | Location: MI | Registered: September 13, 2017 IP