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,

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.

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.

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.

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

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;

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?

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...

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.

..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)

Where are the "MTDCHGS, YTDCHGS..." Fields originated? Are they in the source table? Or DEFINE'd? Or COMPUTE'd fields?

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...

GUI ain't so smart. To make it happy, dumb the defines down to

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

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!

Just a thought: Do you have Only One DEFINE of More Than One DEFINE?

right now ONE define section with 3 items: LOC, MTDCHGS and YTDCHGS....

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....

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

Robert

Which version are you using?

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 . . .
  

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

Are FISCALYEAROFINVCREPD and INVCREPD defined as numeric?

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!

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.

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

anyone see anything wrong with the COMPUTE perhaps?

Include MTHCNT in the report and you might see why it's not working...

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

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....

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...

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...