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] Handling nulls in Running totals

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Handling nulls in Running totals
 Login/Join
 
Silver Member
posted
Hi Everyone,

Can some one explain me how to handle Null values when we are calculating Running Totals.
For Example i have a Across Report.

SET MISSING =ON
-*SET NODATA = 0
TABLE FILE CAR
SUM
SALES
COMPUTE RUNNING_SALES/I6= RUNNING_SALES+ SALES;
BY CAR
WHERE CAR IN ( 'JAGUAR' , 'JENSEN' , 'ALFA ROMEO')
ACROSS COUNTRY
END
-EXIT

             COUNTRY
                   ENGLAND              ITALY
      CAR          SALES RUNNING_SALES  SALES RUNNING_SALES
      ALFA ROMEO     .       .          30200   30200
      JAGUAR       12000   12000          .       .
      JENSEN         0     12000          .       .
      
  



i want to display the report in such a way that missing shuld be 0 and running totals shuld be calculated.


                         COUNTRY
                         ENGLAND              ITALY
            CAR          SALES RUNNING_SALES  SALES RUNNING_SALES
            ALFA ROMEO     0       0          30200   30200
            JAGUAR       12000   12000          0     30200
            JENSEN         0     12000          0     30200
 


Please share any thoughts.

Thanks

This message has been edited. Last edited by: Kerry,


WebFocus 7.1.4 xp html,excel,pdf
 
Posts: 44 | Registered: February 27, 2008Report This Post
Virtuoso
posted Hide Post
Calc the running totals first, then refer to them in a compute after the across:

SET NODATA = 0
TABLE FILE CAR
  SUM COMPUTE C_SALES/I6=SALES + LAST C_SALES; NOPRINT
   BY CAR
  SUM SALES
   BY CAR
  WHERE CAR IN ( 'JAGUAR' , 'JENSEN' , 'ALFA ROMEO')
  ACROSS COUNTRY
  COMPUTE RUNNING_SALES/I6= C2;
END  


C2 is the second verb object in the internal matrix (C_SALES)


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
<FreSte>
posted
souji,

In your example one can (mis)read the report.
It looks like JAGUAR and JENSEN has RUNNING_SALES of 30200 in ITALY.

Maybe you can usethis instead (just a thought)

-Fred

SET NODATA = 0

TABLE FILE CAR
  SUM
    SALES/I9C
    COMPUTE RUNTOT/I9C = 
    IF COUNTRY EQ LAST COUNTRY 
       THEN LAST RUNTOT + SALES
       ELSE SALES;
  BY COUNTRY
  BY CAR
  ON TABLE HOLD AS HLD1
END

TABLE FILE HLD1
  SUM 
    SALES
    RUNTOT
  BY CAR
  ACROSS COUNTRY
  ON TABLE SET PAGE NOPAGE
  ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *
  TYPE=REPORT     ,UNITS=PTS   ,FONT='VERDANA', SIZE=8        ,$
  TYPE=REPORT     ,BORDER=1    ,BORDER-COLOR=RGB(210 210 210) ,$
  TYPE=TITLE      ,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)    ,$
  TYPE=ACROSSTITLE,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)    ,$
  TYPE=ACROSSTITLE,JUSTIFY=CENTER                             ,$
  TYPE=ACROSS     ,JUSTIFY=CENTER                             ,$
  TYPE=SUBTOTAL   ,STYLE=BOLD  ,BACKCOLOR=RGB(210 210 210)    ,$
  TYPE=DATA       ,TOPGAP=2    ,BOTTOMGAP=2                   ,$
  TYPE=DATA       ,BACKCOLOR=(RGB(255 255 255) RGB(245 245 245)),$
ENDSTYLE
END
 
Report 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] Handling nulls in Running totals

Copyright © 1996-2020 Information Builders