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     Missing Data on Across report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Missing Data on Across report
 Login/Join
 
Silver Member
posted
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
 
Posts: 44 | Registered: February 27, 2008Report This Post
Virtuoso
posted Hide Post
Though 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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Silver Member
posted Hide Post
i 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
 
Posts: 44 | Registered: February 27, 2008Report This Post
Virtuoso
posted Hide Post
quote:
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Silver Member
posted Hide Post
You 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
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Silver Member
posted Hide Post
Emily 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
 
Posts: 44 | Registered: February 27, 2008Report This Post
Virtuoso
posted Hide Post
souji, 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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Silver Member
posted Hide Post
hi 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
 
Posts: 44 | Registered: February 27, 2008Report 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     Missing Data on Across report

Copyright © 1996-2020 Information Builders