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     [SOLVED] Trying to figure out some DEFINE logic....

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Trying to figure out some DEFINE logic....
 Login/Join
 
Virtuoso
posted
Hi all,

So I have a data source that has 2 fields: CSALES (sales for this year) and PSALES (sales for last year). I need to figure out how to create a COMPUTE that makes PSALES equal to zero if CSALES is equal to zero for a given weekday (another field WEEKDAY) of a given week (field FISCALWEEKOFYEAR).

So, logically, it would be something along the lines of:
FOREACH (WEEKDAY IN FISCALWEEKOFYEAR) {
IF CSALES EQ 0 THEN PSALES EQ 0 ELSE PSALES;
}


So far, my request looks as follows (how it was originally from the last dev):

TABLE FILE tablename
SUM
CSALES PSALES
WHERE FISCALYEAR EQ &FISCALYEAR;
WHERE FISCALWEEKOFYEAR EQ &FISCALWEEKOFYEAR;
WHERE DSDIVISION EQ &DSDIVISION;
WHERE DSDISTRICT EQ &DSDISTRICT;
WHERE DSBANNERGL EQ &DSBANNERGL;
WHERE DSACCTNO   EQ &DSACCTNO;
WHERE DDDEPTCODE EQ &DDDEPTCODE;
WHERE CSALES NE 0;
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN


Is there a way to create a COMPUTE like I've detailed above somehow?

Any help is/will be much appreciated! Thanks!

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


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Expert
posted Hide Post
A DEFINE should do the trick. You want to do this for each row that's retrieved before they're SUMmed into one report row.


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, 2005Report This Post
Virtuoso
posted Hide Post
Francis,

Thanks for you insights on this. How would you structure your DEFINE to cover the FOREACH?? That's what I'm hung up on... I feel like a goob not being able to figure out some possibly simple logic.


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Virtuoso
posted Hide Post
After much ado...

DEFINE FILE tablename
PSALES/D15=IF WEEKDAY EQ WEEKDAY AND CSALES EQ 0 THEN PSALES EQ 0 ELSE PSALES;
END


Did I do that right, you think? haha Hopefully...


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Expert
posted Hide Post
quote:
DEFINE FILE tablename
PSALES/D15 = IF WEEKDAY EQ WEEKDAY AND CSALES EQ 0 THEN 0 ELSE PSALES;
END


Slight modification Smiler

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by CoolGuy:
IF WEEKDAY EQ WEEKDAY


This condition is always true. It's not clear enough what it's purpose is to provide a better suggestion though.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
Tony,

Thanks for that! Will see if that helps. Not sure if this statement works or not as of yet...

Wep5622,

It's my attempt at getting: for each weekday value in the weekday field (since DEFINEs are for every row). Kind of like iterating through the list (values 1-7). Hopefully that was how I was supposed to do it...

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


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Expert
posted Hide Post
quote:
It's my attempt at getting: for each weekday value in the weekday field (since DEFINEs are for every row). Kind of like iterating through the list (values 1-7).


What does this mean? Do you have seven columns, one for each day? Or do you have seven rows per dimension?


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, 2005Report This Post
Virtuoso
posted Hide Post
Francis,

I am referring to 1 column WEEKDAY and its 7 values that are the numbers 1 through 7 for each day of the week.

My thoughts are that because this is within a DEFINE, every time this expression is evaluated it is evaluated for every row/record. So the WEEKDAY value will be different depending on what record is being read and "defined" in the current iteration/run through the data source that meets the request requirements. So, in a way, this is a "foreach" type loop that iterates through a set number of times because there is a finite number of records/possibilities.


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Master
posted Hide Post
Well considering that you filter out any records where CSALES equals 0... The whole define is irrelevant anyways... Your just aggregating PSALES anyways.

my guess is this is way easier in reality then it is in your head. There is no need for foreach logic in WF. A define is basically what you are thinking of FOREACH. The way that I teach people these concepts is just think of it as an excel sheet. When you copy down a formula it applies to each row in that excel sheet individually right? A define is basically that. So all you need is this:

 

DEFINE FILE TABLENAME
SOME_SALES/D12.2=IF CSALES EQ 0 THEN 0 ELSE PSALES;
END
TABLE FILE tablename
SUM
CSALES PSALES SOME_SALES
WHERE FISCALYEAR EQ &FISCALYEAR;
WHERE FISCALWEEKOFYEAR EQ &FISCALWEEKOFYEAR;
WHERE DSDIVISION EQ &DSDIVISION;
WHERE DSDISTRICT EQ &DSDISTRICT;
WHERE DSBANNERGL EQ &DSBANNERGL;
WHERE DSACCTNO   EQ &DSACCTNO;
WHERE DDDEPTCODE EQ &DDDEPTCODE;
-* TAKE THIS OUT.  COMPLETELY IRRELEVANT
-*WHERE CSALES NE 0;
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN
 


If I were to put this into excel I would do it this way

Column A    |    Column B    |    Column C    |    Column D        | Result of D
WeekDay     |    CSALES      |    PSALES      |    SOME_SALES      |  
Monday      |    0           |    200         |    =if(B2=0,0,C2)  |  0
Tuesday     |    300         |    100         |    =if(B3=0,0,C3)  |  100
Wednesday   |    250         |    400         |    =if(B4=0,0,C4)  |  400
Thursday    |    0           |    300         |    =if(B5=0,0,C5)  |  0
...
...
...
Totals      |    550         |    1000        |                    |  500

etc.


A compute will do the calulation after the summary. Its like if I only applied the calculation to the Total line. In this case the value you would get would be 1000 since you don't have any BY statements in your query. From what you have described, your total result should have been 500 in the example above. That is the code I posted.

if you need to validate that it is doing this at a line by line basis, Use the PRINT verb instead of SUM. It will show you the values line by line.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
Just re-read your last post. I think I understand a bit better what might be the issue with the define. You have data like this:

Date      |    WEEKDAY     |    CSALES    |    PSALES    |
2/1/2016  |      1         |    500       |    400       |
2/8/2016  |      1         |    0         |    300       |
2/9/2016  |      2         |    400       |    600       |
2/15/2016 |      1         |    300       |    300       |
2/17/2016 |      3         |    0         |    200       |

  


So you would expect for weekday 1 a value of 1000
For weekday 2 600
for weekday 3 0

for a total of 1600

If you do this as a define you would a value of 700 for weekday 1
For weekday 2 600
and weekday 3 0

for a total of 1300

what you want to see in your output is CSALES of 1200 PSALES 1800 and SOME_SALES as 1600


to achieve that do this

 

TABLE FILE tablename
SUM
CSALES PSALES 
COMPUTE SOME_SALES/D12.2=IF CSALES EQ 0 THEN 0 ELSE PSALES;
BY WEEKDAY
WHERE FISCALYEAR EQ &FISCALYEAR;
WHERE FISCALWEEKOFYEAR EQ &FISCALWEEKOFYEAR;
WHERE DSDIVISION EQ &DSDIVISION;
WHERE DSDISTRICT EQ &DSDISTRICT;
WHERE DSBANNERGL EQ &DSBANNERGL;
WHERE DSACCTNO   EQ &DSACCTNO;
WHERE DDDEPTCODE EQ &DDDEPTCODE;
-* TAKE THIS OUT.  COMPLETELY IRRELEVANT
-*WHERE CSALES NE 0;
ON TABLE HOLD AS MY_SALES_HLD
END

TABLE FILE MY_SALES_HLD
SUM CSALES PSALES SOME_SALES
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
END
 


Or you can always put a total at the end of the report if you don't mind seeing it broken out by weekday first.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Virtuoso
posted Hide Post
Eric,

Thanks so much for the clarification! I thought I knew what I was doing, but come to learn that I'm still learning... lol

Good explanation. I actually did take out the WHERE CSALES NE 0 earlier and forgot to mention that. Thanks though.

So, basically I have some KPIs up top that showcase values for this year's sales/guest counts compared to last year's sales/guest counts WTD. Some KPIs seem to show the right values while others show the previous year's values for the entire week versus just to the current day with sales/guest counts for this year in comparison. So, to clarify, I want the KPIs to show values for up to Tuesday this year and Tuesday last year if that was the last day that had data loaded for it this week.

I also have vertical bar graphs below these KPIs that showcase the above KPI values across the week selected to view. These graphs show correct values if I have the WHERE CSALES NE 0 in place (or WHERE CCUSTTOTAL NE 0), but then I no longer get the days beyond the date with CSALES or CCUSTTOTAL counts in them to render as well showing the PSALES/PCUSTTOTAL values for those days...which my project lead wants to still see. If I take out the WHERE CSALES NE 0 I get the whole week to render with previous year's values for future days also, but the values are off. They jump/increase slighty in value for some reason. Not sure why.

My graphs look like the following (Sunday through Tuesday values for PSALES/PCUSTTOTAL are off from values in KPIs if I leave out the WHERE .... NE 0 clauses):

...........................................................................................................................
.                   _                                                                              _                  _   .
.  _                _                                                                              _                  _   .
.  _       _        _                _                                            _                _                  _   .
.  _       _        _        _       _        _                 _                 _                _                  _   .
.  _       _        _        _       _        _                 _                 _                _                  _   .
.  _       _        _        _       _        _                 _                 _                _                  _   .
CSALES | PSALES  CSALES | PSALES  CSALES | PSALES   CSALES | PSALES   CSALES | PSALES   CSALES | PSALES   CSALES | PSALES
  Sunday              Monday          Tuesday           Wednesday         Thursday          Friday            Saturday


If I add the new logic we've discussed as a DEFINE (below), the graphs then show all weekdays for the week and have correct values finally that match with the KPIs, but then still leave out the values/bars for PSALES, etc. for future days in the week. The days without CSALES/CCUSTTOTAL are just blank.

DEFINE FILE tablename
PCUSTTOTAL/I11 = IF WEEKDAY EQ WEEKDAY AND CCUSTTOTAL EQ 0 THEN 0 ELSE PCUSTTOTAL;
END

and then take out:

-* WHERE CCUSTTOTAL NE 0;


Again, if I have the WHERE CCUSTTOTAL/CSALES NE 0 in place I get correct values, but then I lose the rest of the PCUSTTOTAL/PSALES values for weekdays without CCUSTTOTAL/CSALES... So my graph only has bars for the days that have had sales this week so far.

Here's my graph code request:

DEFINE FILE tablename
PCUSTTOTAL/I11 = IF WEEKDAY EQ WEEKDAY AND CCUSTTOTAL EQ 0 THEN 0 ELSE PCUSTTOTAL;
END
TABLE FILE tablename
SUM
CCUSTTOTAL
PCUSTTOTAL
BY FISCALYEAR
BY FISCALWEEKOFYEAR
BY WEEKDAY
BY WEEKDAYNAME
BY WEEKDAYNAMESHORT
BY DATEFULL
WHERE FISCALYEAR       EQ &FISCALYEAR;
WHERE FISCALWEEKOFYEAR EQ &FISCALWEEKOFYEAR;
WHERE DSDIVISION       EQ &DSDIVISION;
WHERE DSDISTRICT       EQ &DSDISTRICT;
WHERE DSBANNERGL       EQ &DSBANNERGL;
WHERE DSACCTNO         EQ &DSACCTNO;
WHERE DDDEPTCODE       EQ &DDDEPTCODE;
-*WHERE (CCUSTTOTAL NE 0);
ON TABLE HOLD
END
-RUN

GRAPH FILE HOLD
SUM
CCUSTTOTAL AS 'Current Year'
PCUSTTOTAL AS 'Prior Year'
BY WEEKDAY NOPRINT
BY WEEKDAYNAMESHORT AS 'Weekday'
ON GRAPH PCHOLD FORMAT JSCHART
...


This is quite frustrating to get correct values for week days if I chop out the future days of the week, but then get incorrect values if I keep the future days of the week for previous year's values in the picture...

Anyone here that can see what I may be doing wrong, and would still be willing to help, I would really appreciate it.

Thanks!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Virtuoso
posted Hide Post
quote:
DEFINE FILE tablename
  PCUSTTOTAL/I11 = IF WEEKDAY EQ WEEKDAY AND CCUSTTOTAL EQ 0 THEN 0 ELSE PCUSTTOTAL;
END


When evaluating this expression, you're looking at a single row at a time. A single row only has a single value for WEEKDAY. The above is in effect equivalent to:
DEFINE FILE tablename
  PCUSTTOTAL/I11 = IF CCUSTTOTAL EQ 0 THEN 0 ELSE PCUSTTOTAL;
END


This define overrides the actual value of PCUSTTOTAL in the row with a new value that's either 0 (when CCUSTTOTAL EQ 0) or the original value of that field. That's a dangerous thing to do, and I think I've seen problems when doing that where WF can't decide which field is which and ends up adding the result to the result or some such.

My guess is that in fact you want to reference a previous row for parts of those values, in which case you need to use a COMPUTE and LAST, in which case your original definition makes more sense too.
Are you in fact attempting to compute a cumulative sum?:
  COMPUTE PCUSTTOTALTOTAL/I11 = IF WEEKDAY EQ LAST WEEKDAY AND CCUSTTOTAL EQ 0 THEN LAST PCUSTTOTALTOTAL ELSE LAST PCUSTTOTALTOTAL + PCUSTTOTAL;


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
Wep5622,

Thanks for your insights and help with this. We actually finally figured out why we were having all the issues we were having with these KPIs and graphs. Not a few things either.

Things I discovered and fixed:

- 2 of our stores changed banners and store numbers. We had previous year data for their old banners/store #s and current year data for their new ones. Resolution: We added some DEFINE statements to merge the 2 together.

- We were missing sales data for certain stores that was throwing our COMPUTEs for sales/labor hour off. Resolution: Filtered those stores out for now.

- Our KPIs were not bringing back just the WTD values but the full week values for previous year data due to how things were configured with the requests. Added some additional defines and even a hold up top to get things ironed out.


Thanks to everyone that tried to get me through these issues we were having! Appreciate you all!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report 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     [SOLVED] Trying to figure out some DEFINE logic....

Copyright © 1996-2020 Information Builders