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,

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,

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.

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

quote:

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

Slight modification

T

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.

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,

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,

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.

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.

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,

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!

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;

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!