Focal Point
Missing Data on Across report
November 08, 2011, 07:55 AM
soujiMissing Data on Across report
i have a master file TOTSHT
TABLE FILE TOTSHT
PRINT
MDSE_GRP_N
SHTG
ACCT_YR_I NOPRINT
ACCT_MO_N
ACCT_WK_N
WHERE ACCT_MO_N EQ 'APRIL'
ON TABLE HOLD AS TRGFIN
END
The result is
MDSE_GRP_N SHTG ACCT_MO_N ACCT_WK_N
APPAREL/ACCESS 0.87 APRIL APR1
HARDLINES .12 APRIL APR1
HOME 0.4 APRIL APR1
GROCERY 1.2 APRIL APR1
HEALTH/BEAUTY 1.0 APRIL APR1
APPAREL/ACCESS .76 APRIL APR2
HARDLINES .18 APRIL APR2
HOME 0.7 APRIL APR2
GROCERY 1.7 APRIL APR2
HEALTH/BEAUTY 1.8 APRIL APR2
GROCERY 1.4 APRIL APR3
GROCERY 1.1 APRIL APR4
When i try with across the out put is
TABLE FILE TRGFIN
SUM
SHTG
-**********YTD CALCULATION
COMPUTE SHTGIT/P15.2=SHTGIT+SHTG; NOPRINT
BY ACCT_YR_I NOPRINT
BY DUMMY NOPRINT
BY ACCT_MO_I NOPRINT
BY V_BLANK NOPRINT
BY V_WK_N
ACROSS MDSE_GRP_N AS ''
END
APPAREL HL H/B HOME GROCERY
Apr Wk 1 .87 .12 1.0 0.4 1.2
Apr Wk 2 .76 .18 1.8 0.7 1.7
Apr Wk 3 - - - - 1.4
Apr Wk 4 - - - - 1.1
MTD MTD Val
YTD 0.0 0.0 0.0 0.0 5.4
we dont have all MDSE_GRP_N for APril week 3 and 4 except Grocery.i want to display the missing values with 0.00 and use that for my YTD calculation.
I tried with Missing ON in my compute but it didnt help.
My YTD calculation is
COMPUTE SHTGIT/P15.2=SHTGIT+WTD_TOT_SHTG_A; NOPRINT
iam using this in my subfoot.
How to get this done?
APPAREL HL H/B HOME GROCERY
Apr Wk 1 .87 .12 1.0 0.4 1.2
Apr Wk 2 .76 .18 1.8 0.7 1.7
Apr Wk 3 .0 .0 .0 .0 1.4
Apr Wk 4 .0 .0 .0 .0 1.1
MTD MTD Val
YTD 16.3 .30 2.8 0.0 5.4
Appretiate any ideas.
Thanks.
WebFocus 7.1.4 xp html,excel,pdf
November 08, 2011, 08:53 AM
njsdenThough I haven't tested this, you can do your subtotal calculations using RECAP andd displaying them in a SUBFOOT as you said. I think that even if there are MISSING values, RECAP will still use the proper aggregated value (SET MISSING=ON may still be needed).
As for displaying 0.00 in place of MISSING, you can play with SET NODATA.
Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
November 09, 2011, 03:42 AM
soujii tried with SET MISSING=ON it didnt help.
i want to use it for YTD calculation if i use SET NODATA it will be for display.
WebFocus 7.1.4 xp html,excel,pdf
November 09, 2011, 08:52 AM
njsdenquote:
if i use SET NODATA it will be for display
Well:
quote:
As for displaying 0.00 in place of MISSING, you can play with SET NODATA
That's exactly what I had said. Did you try using
RECAP to see if it would aid in creating your YTD calculations?
Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
November 09, 2011, 09:51 AM
Emily MaxYou need to use NODATA along with EMPTYCELLS. I recently used the following set commands successfully to do the exact same thing with an across report.
SET EMPTYCELLS = OFF
SET NODATA = '0.00'
Happy Coding,
Emily
WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
November 10, 2011, 07:21 AM
soujiEmily i was able to display the 0.0 when there is no data.but that totals are not displayed in YTD section.In my across report if the value is null or 0 then YTD totals also displaying 0 for that month.Below is my code.
DEFINE FILE &TRG_FIN
V_WK/A3 = LCWORD( 3, EDIT(ACCT_WK_N,'999'), V_WK);
V_WK_N/A10 = V_WK |' Wk '| EDIT(ACCT_WK_N,'$$$9');
DUMMY/A1='';
V_BLANK/A1 = '';
V_OR/I2=DECODE V_GROUP('APPAREL' 9 'HL' 8 'H/B' 7 'HOME' 6 'GROCERY' 5 'Region100' 4 'Region200' 3 'Region300' 2 'Region400' 1);
END
TABLE FILE &TRG_FIN
SUM
COMPUTE CY_RATE/P15.2CB=(WTD_TOT_SHTG_A/WTD_BTWN_SLS_A)*100; AS 'SHTG %'
COMPUTE BP_LY/P15.2CB=((LY_TOT_SHTG_A/LY_BTWN_SLS_A)*100)-((WTD_TOT_SHTG_A/WTD_BTWN_SLS_A)*100); AS 'B/(P),LY'
-*********YTD TOTLAS************
COMPUTE SHTGIT/P15.2=SHTGIT+WTD_TOT_SHTG_A; NOPRINT
COMPUTE SLSIT/P15.2=SLSIT+WTD_BTWN_SLS_A; NOPRINT
COMPUTE LYSHTIT/P15.2=LYSHTIT+LY_TOT_SHTG_A; NOPRINT
COMPUTE LYSLIT/P15.2=LYSLIT+LY_BTWN_SLS_A; NOPRINT
COMPUTE YTD_SHT/P15.1CB =IF YTD_SHT IS MISSING THEN 0.0 ELSE (SHTGIT/1000000); NOPRINT
COMPUTE YTD_SLS/P15.1CB=SLSIT/1000000; NOPRINT
COMPUTE YTD_CYR/P15.2CB=(SHTGIT/SLSIT)*100; NOPRINT
COMPUTE YTD_BPLY/P15.2CB=((LYSHTIT/LYSLIT)*100)-((SHTGIT/SLSIT)*100); NOPRINT
-***************
BY ACCT_YR_I NOPRINT
BY DUMMY NOPRINT
BY ACCT_MO_I NOPRINT
BY V_BLANK NOPRINT
BY V_WK_N AS 'ACCT, WK'
BY SEG_STAT_C NOPRINT
ACROSS HIGHEST V_OR NOPRINT
ACROSS V_GROUP AS ''
ON V_BLANK RECOMPUTE AS 'MTD'
ON V_BLANK SUBFOOT
"YTD<C14<+0><C15<+0><C29<+0><C30<+0><C44<+0><C45<+0><C59<+0><C60<+0><C74<+0><C75<+0><C89<+0><C90<+0><C104<+0><C105<+0><C119<+0><C120<+0><C134<+0><C135"
ON TABLE RECOMPUTE AS ''
YTD results also displaying zero as i mentioned in my previous post.
Thanks
WebFocus 7.1.4 xp html,excel,pdf
November 10, 2011, 10:41 AM
njsdensouji, would you please write a sample code illustrating your issue (and expectation) using the CAR or GGSALES table so we are in a better position to see what's going on and perhaps help you better?
Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
November 14, 2011, 05:12 AM
soujihi njsdn,
I really dont know to put my scenario with car file.
I have an across report with 2 compute fields displaying by accounting week.Iam calculating MTD Totlas(i used recompute for this) and running totls to display YTD values.for April week 3 and week 4 there is no data.(default it displays .)When iam calculating running totals for April week3 and 4 it is showing the values 0 instead of my previous value.
Please check my code below.
Iam sorry if tihs post is already there.please suggest some ideas how to fix this.
TABLE FILE &TRG_FIN
SUM
COMPUTE CY_RATE/P15.2CB=(WTD_TOT_SHTG_A/WTD_BTWN_SLS_A)*100; AS 'SHTG %'
COMPUTE BP_LY/P15.2CB=((LY_TOT_SHTG_A/LY_BTWN_SLS_A)*100)-((WTD_TOT_SHTG_A/WTD_BTWN_SLS_A)*100); AS 'B/(P),LY'
-*********TOTLAS************
COMPUTE SHTGIT/P15.2 =SHTGIT+WTD_TOT_SHTG_A; NOPRINT
COMPUTE SLSIT/P15.2=SLSIT+WTD_BTWN_SLS_A; NOPRINT
COMPUTE LYSHTIT/P15.2=LYSHTIT+LY_TOT_SHTG_A; NOPRINT
COMPUTE LYSLIT/P15.2=LYSLIT+LY_BTWN_SLS_A; NOPRINT
-******CALCULATING RUNNING TOTALS******
COMPUTE YTD_CYR/P15.2CB=(SHTGIT/SLSIT)*100; NOPRINT
COMPUTE YTD_BPLY/P15.2CB=((LYSHTIT/LYSLIT)*100)-((SHTGIT/SLSIT)*100); NOPRINT
-***************
BY ACCT_YR_I NOPRINT
BY DUMMY NOPRINT
BY ACCT_MO_I NOPRINT
BY V_BLANK NOPRINT
BY V_WK_N AS 'ACCT, WK'
BY SEG_STAT_C NOPRINT
ACROSS HIGHEST V_OR NOPRINT
ACROSS V_GROUP AS ''
ON V_BLANK RECOMPUTE AS 'MTD'
ON V_BLANK SUBFOOT
"YTD<C14<+0><C15<+0><C29<+0><C30<+0><C44<+0>.....and so on
END
[IMG:left]
[/IMG]
WebFocus 7.1.4 xp html,excel,pdf