Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved]Getting Prev Year Count
Go
New
Search
Notify
Tools
Reply
  
[Solved]Getting Prev Year Count
 Login/Join
 
Platinum Member
posted
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, 2017Reply With QuoteReport This Post
Virtuoso
posted 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, 2007Reply With QuoteReport This Post
Master
posted 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, 2006Reply With QuoteReport This Post
Platinum Member
posted 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, 2017Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved]Getting Prev Year Count

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.