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     [CLOSED] MTD and YTD totals...whats the simplest way?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] MTD and YTD totals...whats the simplest way?
 Login/Join
 
Master
posted
It’s been a while since I did any coding with the BI tool. I have a simple request.

The user will select a DEPT, FY and Period. Period is essentially a numeric representation of the month (201707 = July etc)
The report must show DEPT, MTD Charges and YTD Charges.
The table to be queried has a simple layout: DEPT , FY, PD and CHGS.

As an example, the user might select the DEPT = East, FY = 2018 and as of Period = 201802.
The report should bring back both the MTD and YTD charges for the EAST as of 201802.

My selection is of course based on the DEPT, the FY and the PD less than or equal to the as of Period Specified.
If I was just doing YTD – no problem. YTD is simply be the sum of CHGS; ie the sum of everything selected.
However for MTD I need to do a calc item that says if the PD = the period selected by the user, then charges, otherwise zero.
How do I do that? It feels like it should be simple.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
I would define...
  YTD_CHGS/P12.2=((FY EQ &FY) AND ((PD LE &Period)) * CHGS;
  MTD_CHGS/P12.2=(Period EQ &Period) * CHGS;

A logical expression evaluates to 0 (if false) or 1 (if true);
so summing a DEFINE of the form
(logical condition) * var
gives the sum of var over the instances where the logical condition is true.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
that almost seems too easy! I will try it. It's been a while for me in WebFocus..I started of trying a calculated field but I could not reference the parameters there.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
quote:
I started of trying a calculated field but I could not reference the parameters there.

You mean within a COMPUTE? Please supply the code that didn't work within the </> as shown in red above. Variables / parameters are available in COMPUTEs.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
quote:
YTD_CHGS/P12.2=((FY EQ &FY) AND ((PD LE &Period)) * CHGS;
MTD_CHGS/P12.2=(Period EQ &Period) * CHGS;

no luck. Syntax issue? I do not see the MTD_CHARGES and YTD_CHARGES in the list of items to drag to the report. Ideas?


DEFINE FILE DATATBL
LOC/A10=IF IDXLOCATION EQ 'Office' THEN 'Office' ELSE 'Other';

YTD_CHGS/P12.2=((FISCALYEAROFINVCREPD EQ &FISCALYEAROFINVCREPD) AND ((INVCREPD LE &INVCREPD)) * TOTALCHARGES;

MTD_CHGS/P12.2=(INVCREPD EQ &INVCREPD) * TOTALCHARGES;
END

TABLE FILE DATATBL
SUM
TOTALCHARGES AS ''
BY LOWEST LOC AS ''
HEADING
" "
"&RPTPROVIDERNAME "
" "
"Gross Charges"
WHERE (FISCALYEAROFINVCREPD EQ &FISCALYEAROFINVCREPD.(FIND DATATBL.DATATBL.FISCALYEAROFINVCREPD,DATATBL.DATATBL.FISCALYEAROFINVCREPD IN DATATBL).FISCALYEAROFINVCREPD:. );

WHERE (INVCREPD LE &INVCREPD.(FIND DATATBL.DATATBL.INVCREPD,DATATBL.DATATBL.INVCREPD IN DATATBL).INVCREPD:. ) ;

WHERE (DATATBL.DATATBL.RPTPROVIDERNAME EQ '&RPTPROVIDERNAME.(FIND DATATBL.DATATBL.RPTPROVIDERNAME,DATATBL.DATATBL.RPTPROVIDERNAME IN DATATBL).RPTPROVIDERNAME:.' );

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
Try using ".EVAL" :

Change:
YTD_CHGS/P12.2=((FY EQ &FY) AND ((PD LE &Period)) * CHGS;
MTD_CHGS/P12.2=(Period EQ &Period) * CHGS;

To:
YTD_CHGS/P12.2=((FY EQ &FY.EVAL) AND ((PD LE &Period.EVAL)) * CHGS;
MTD_CHGS/P12.2=(Period EQ &Period.EVAL) * CHGS;
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
quote:
To:YTD_CHGS/P12.2=((FY EQ &FY.EVAL) AND ((PD LE &Period.EVAL)) * CHGS;
MTD_CHGS/P12.2=(Period EQ &Period.EVAL) * CHGS;



Nope..I cannot see them in the list..to drag onto the report....Is one of those times where you can do it in the code but the GUI does not recognize it?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
when I view the code in the GUI and check errors it says none...though I am not sure how much you can rely on that check...


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
ok, there is a missing a '(' missing in the YTD_CHGS I think.

I still can not see them in the GUI but if I code them ontot the report suddenly values appear. MTD look correct. No idea what YTD is doing....does not seem to work...however YTD is really just the sum of all chgs selected...SUM CHGS....that works.

If you see a reason the YTD does not work, let me know.

I'd still like to know why the gui does not recognize these defines.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
..and finally...YTDCHGS works:

YTDCHGS/P12.2=((FY EQ &FY.EVAL) AND (PD LE &PD.EVAL)) * CHGS;

Still when I double click to go into the GUI it says: The following fields were not recognized....MTDCHGS, YTDCHGS....

It works...but I had to manually code it....would really like the GUI to recognize it...or is this some sort of bug from being in such an old version of WF (8)


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
Where are the "MTDCHGS, YTDCHGS..." Fields originated? Are they in the source table? Or DEFINE'd? Or COMPUTE'd fields?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
Defines...see post above...the weird thing is the LOC define appears in the GUI for me to drag onto the report..not the YT and MTD defines though...


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
GUI ain't so smart. To make it happy, dumb the defines down to

=if (...) then CHGS else 0;


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
I will try that! TX. Everything worked but now that added something new.

So, I show YTD charges. They want an average. It is defined as the number of months YTD (and that's fiscal year to date...uhhg!) divided by total charges.

My period is of the format YYYMM, ie 201707 = July 2017....the is would be Month 1, 201708 would be 2 months etc. I guess I need another define to evaluate the last two digits; ie 07 = 1, 08=2, 09 = 3 etc.....no idea how complex that is or how much it would slow the fex down.

I could add a column to the masterfile (Fiscal Month Number) I suppose...

Either way, I think I then need a COMPUTE no? Would I not need to compute YTDCHGS / Fiscal Month Number?

Can I do a compute using a defined 2 field(YTDCHGS / Fiscal Month Number )

tx again!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
Just a thought: Do you have Only One DEFINE of More Than One DEFINE?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
right now ONE define section with 3 items: LOC, MTDCHGS and YTDCHGS....


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
btw...made changes:

YTDCHGS/P12.2= IF((FY EQ &FY.EVAL) AND (PD LE &PD.EVAL)) THEN TOTALCHARGES ELSE 0;

The GUI still does not recognize ..they work...no deal on GUI tho....
Frowner


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
this even works...but not seen in GUI..wonder if the GUI in the latest version is better?

IF((FISCALYEAROFINVCREPD EQ &FISCALYEAROFINVCREPD) AND (INVCREPD LE &INVCREPD)) THEN TOTALCHARGES ELSE 0


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
Robert

Which version are you using?


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
for month and ytd average charge amount (pseudocode, untested):
define ...
  mtd_count/i5=if (...) then 1 else 0;
  ytd_count/i5=if (...) then 1 else 0;
  mtd_charges=charges * mtd_count;
  ytd_charges=charges * ytd_count;

table ...
sum    mtd_charges as 'mtd total charges'
       mtd_count noprint
compute
       mtd_avg_charge = mtd_charges / mtd_count; as 'mtd average'

       ytd_charges as 'ytd total charges'
       ytd_count noprint
compute
       ytd_avg_charge = ytd_charges / ytd_count; as 'ytd average'

by . . .
where . . .
  


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
IF((FISCALYEAROFINVCREPD EQ &FISCALYEAROFINVCREPD) AND (INVCREPD LE &INVCREPD)) THEN TOTALCHARGES ELSE 0


Are FISCALYEAROFINVCREPD and INVCREPD defined as numeric?
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
We are on WF 8008.

FY is D20.2 - no idea why..I just updated the master file to make it I4

PD is I11
.
.
.
still GUI no likey!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
Robert

8008 is a very old version. If you can repro this with a sample file like ggorder we can try it in a newer version and see if it behaves better. You may want to open a case with techsupport on this as well since none of the suggestions are helping.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Master
posted Hide Post
yea...no big deal...GUI or not that report works.

My issue is with now calculating Average charges per month. I created another report for testing. SEE CODE BELOW!

Notice I selected my Period (&PD) to be: 201804. That is April 2018. April is 10 months into our Jul-Jun Fiscal year. I want to take my total charges and divide by 10.

When I run the report my total charges are correct. If I bring MTHCNT (the number of months YTD based on the requested period), I indeed see 10! However the COMPUTE is not dividing my total charges by 10...LOL...I have no clue what the heck its doing but its not dividing by 10!!!

Do you see anything?
-SET &FY = 2018;
-SET &PD = 201804;
-SET &PROV = 'BERGER, ALAN';
DEFINE FILE TBLTEMP_CHGSUNITSPMTSRPTBILLSERVPROVIDERFINAL

-* CONVERT PD to string, get month portion, convert back to number
YTDCALPDNBR/I2=EDIT(SUBSTR(9,EDIT(&PD), 8, 9, 2, 'A2') );

-*Evaluate YTCALPDNBR to determine numbers of months into FY
MTHCNT/I2=IF YTDCAlPDNBR EQ 1 THEN 7 ELSE
IF YTDCAlPDNBR EQ 2 THEN 8 ELSE
IF YTDCAlPDNBR EQ 3 THEN 9 ELSE
IF YTDCAlPDNBR EQ 4 THEN 10 ELSE
IF YTDCAlPDNBR EQ 5 THEN 11 ELSE
IF YTDCAlPDNBR EQ 6 THEN 12 ELSE
IF YTDCAlPDNBR EQ 7 THEN 1 ELSE
IF YTDCAlPDNBR EQ 8 THEN 2 ELSE
IF YTDCAlPDNBR EQ 9 THEN 3 ELSE
IF YTDCAlPDNBR EQ 10 THEN 4 ELSE
IF YTDCAlPDNBR EQ 11 THEN 5 ELSE 6
END
TABLE FILE TBLTEMP_CHGSUNITSPMTSRPTBILLSERVPROVIDERFINAL
SUM
TOTALCHARGES AS 'TTLCHGS'
COMPUTE AVGRF2/D12.2 = TOTALCHARGES / MTHCNT;
WHERE ( FISCALYEAROFINVCREPD EQ &FY);
WHERE ( INVCREPD LE &PD);
WHERE ( RPTPROVIDERNAME EQ '&PROV');
END


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
anyone see anything wrong with the COMPUTE perhaps?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
Include MTHCNT in the report and you might see why it's not working...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
I did yesterday...Charges = 803K, mthcnt = 10 but AVG = 50.

-SET &FY = 2018;
-SET &PD = 201804;
-SET &PROV = 'BERGER, ALAN';
DEFINE FILE TBLTEMP_CHGSUNITSPMTSRPTBILLSERVPROVIDERFINAL
YTDCAlPDNBR/I2=EDIT(SUBSTR(9,EDIT(&PD), 8, 9, 2, 'A2') );
MTHCNT/I2=IF YTDCAlPDNBR EQ 1 THEN 7 ELSE
IF YTDCAlPDNBR EQ 2 THEN 8 ELSE
IF YTDCAlPDNBR EQ 3 THEN 9 ELSE
IF YTDCAlPDNBR EQ 4 THEN 10 ELSE
IF YTDCAlPDNBR EQ 5 THEN 11 ELSE
IF YTDCAlPDNBR EQ 6 THEN 12 ELSE
IF YTDCAlPDNBR EQ 7 THEN 1 ELSE
IF YTDCAlPDNBR EQ 8 THEN 2 ELSE
IF YTDCAlPDNBR EQ 9 THEN 3 ELSE
IF YTDCAlPDNBR EQ 10 THEN 4 ELSE
IF YTDCAlPDNBR EQ 11 THEN 5 ELSE 6
END
TABLE FILE TBLTEMP_CHGSUNITSPMTSRPTBILLSERVPROVIDERFINAL
SUM
TOTALCHARGES AS 'TTLCHGS'
COMPUTE AVGRF2/D12.2 = TOTALCHARGES / MTHCNT;
PRINT MTHCNT
WHERE ( FISCALYEAROFINVCREPD EQ &FY);
WHERE ( INVCREPD LE &PD);
WHERE ( RPTPROVIDERNAME EQ '&PROV');
END


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
for some reason '10' is not getting into the compute...if I hard code it to '10' it works but though the MTHCNT seems to come up with 10 when printed...the compute is not seeing it that way....


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
I would have...
TABLE FILE TBLTEMP_CHGSUNITSPMTSRPTBILLSERVPROVIDERFINAL
SUM 
TOTALCHARGES AS 'TTLCHGS'
MTHCNT
COMPUTE AVGRF2/D12.2 = TOTALCHARGES / MTHCNT;
WHERE ( FISCALYEAROFINVCREPD EQ &FY);
WHERE ( INVCREPD LE &PD);
WHERE ( RPTPROVIDERNAME EQ '&PROV');
END
not
TABLE FILE TBLTEMP_CHGSUNITSPMTSRPTBILLSERVPROVIDERFINAL
SUM 
TOTALCHARGES AS 'TTLCHGS'
COMPUTE AVGRF2/D12.2 = TOTALCHARGES / MTHCNT;
PRINT MTHCNT
WHERE ( FISCALYEAROFINVCREPD EQ &FY);
WHERE ( INVCREPD LE &PD);
WHERE ( RPTPROVIDERNAME EQ '&PROV');
END
Might make a difference...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
quote:
COMPUTE AVGRF2/D12.2 = TOTALCHARGES / MTHCNT;

Nope! did not work...looks like if I dump into a hold file first ... then I can get it to work....I hope newer versions of this tool have cleaned all this up...


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report 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     [CLOSED] MTD and YTD totals...whats the simplest way?

Copyright © 1996-2020 Information Builders