Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Differences B/W values based on dates

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Differences B/W values based on dates
 Login/Join
 
<Umar Farook S>
posted
Hi

I need to calculate a value based on dates being enterd by user
For Example the table data structure i slike this

AcctNo EntryDate Market Value
1 11/27/06 1000
1 11/28/06 2000
1 11/29/06 5000
1 11/30/06 1500
2 11/29/06 1000
2 11/30/06 2500
2 12/01/06 3000
3 11/28/06 4000
3 11/29/06 1000
3 11/30/06 2000

Where entrydate = date entered by the user

When user enters 11/30/06
the output should be like this
AcctNo EntryDdate Market Value
1 11/30/06 -3500
2 11/30/06 1500
3 11/30/06 1000

Market value is calculated by subtracting the previous row market value for same AcctNo from the entrydate row market value(which is entered by the user)
If no previous row is avialable before 11/30/06 it will display the same value as of 11/30/06 in output.
 
Report This Post
FrankDutch
Virtuoso
posted Hide Post
Umar

I remember you posted the same issue last month
What was wrong with the answers you have got at that post

quote:

Posted July 11, 2007 03:42 PM
i have a column called entry date which captures daily date details like account.
i need to find the difference between todays date and yesterdays date amount details.
final amount=
amount with entry date of today - amount with entry date of yesterday

please help me out



https://forums.informationbuilders.com/eve/forums/a/tpc/...971028632#8971028632


We would like to help, but al the answers are there already




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
Hi Frank
Solution that we got works fine only for 2 rows of data.Sinc ein my code i have sorted and selected by Highest 2 dates.
If there are more than 2 rows then it will come different.So only side by side i am also trying out but also looking for some help. I am posting the code which we used last for our calculations

:::CODE:::
SELECT
ASSET_ID,
FULL_MARKET_VALUE,
ENTRY_DATE
FROM MurexODS.ACCOUNTING_BALANCES
;
-*
-*STORING OUTPUT IN A TABLE
TABLE FILE SQLOUT
SUM FULL_MARKET_VALUE
BY HIGHEST 2 ENTRY_DATE
BY ASSET_ID
ON TABLE HOLD AS HOLDNEW
END
-RUN
-*
-*STORING OUTPUT IN A NEW TABLE FOR COMPUTATION
TABLE FILE HOLDNEW
PRINT
FULL_MARKET_VALUE
COMPUTE CURR_MARKET_VALUE/D20.2 = IF ASSET_ID EQ LAST ASSET_ID
THEN FULL_MARKET_VALUE - LAST FULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
BY ASSET_ID
BY ENTRY_DATE
ON TABLE HOLD AS NEWFILENAME
END
-RUN
-*
-*STORING OUTPUT IN A NEW TABLE FOR RESTRICTION
TABLE FILE NEWFILENAME
PRINT
CURR_MARKET_VALUE
BY HIGHEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME1
END
-RUN
-*
 
Report This Post
FrankDutch
Virtuoso
posted Hide Post
quote:
BY HIGHEST 2 ENTRY_DATE
BY ASSET_ID



I think this should be

BY ASSET_ID
BY HIGHEST 2 ENTRY_DATE

I wonder how you can get more than two rows since you ask for the highest 2.

Is your date format a real date? That might be important for the sorting.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
Hi Frank

We donot want highest 2 rows now. we just need to subtract the current row value from the previous row value depending upon the date being entered by the user.
 
Report This Post
FrankDutch
Virtuoso
posted Hide Post
That's nice Umar

But to come to the end result you have to make small stepps and this is one of them.
You want to find the actual value and the previous value, so by running the program sorted on the highest two you get the highest and the previous record.

The next step is to calculate the difference of these two values.

If you want this done based on a day entered by the user you restrict the program with

WHERE ENTRY_DATE LE '&Entered_date';




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Differences B/W values based on dates

Copyright © 1996-2020 Information Builders