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     [CLOSED] MTD and YTD totals...whats the simplest way?
Go
New
Search
Notify
Tools
Reply
  
[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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Just a thought: Do you have Only One DEFINE of More Than One DEFINE?
 
Posts: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Master
posted Hide Post
right now ONE define section with 3 items: LOC, MTDCHGS and YTDCHGS....


WebFOCUS 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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: 1558 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport 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, 2005Reply With QuoteReport 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, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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: 1558 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
anyone see anything wrong with the COMPUTE perhaps?


WebFOCUS 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport 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, 2005Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 537 | Registered: June 28, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.