Hi All,

I have a chart that displays daily sales with a 5 day rolling average. Since our sales on Saturday and Sunday are much lower that during the week it skews the rolling average. What I want to do is roll the sales for Saturday and Sunday into Friday. So far I have not been able to figure this out.

Below is code for the report:

-*Do not delete or modify the comments below
*-INTERNAL_COMMENT LINE#0$PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiIHN0YW5kYWxvbmU9Im5vIj8+DQo8IS0tMS4wLS0+PFJvb3QgcmVsZWFzZT0iOC4yLjAuMSIgdmVyc2lvbj0iMS4yIj4NCiAgICA8T2JqZWN0IG9iamVjdElkPSJUYWJsZUNoYXJ0XzEiPg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iTGlua2VkU29ydHMiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciLz4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9ImNvbXBvbmVudEN1c3RvbVRpdGxlIiB0eXBlPSJqYXZhLmxhbmcuQm9vbGVhbiI+ZmFsc2U8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iRmlsdGVyc1N0YXR1cyIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+JmFtcDtsdDs/eG1sIHZlcnNpb249JmFtcDtxdW90OzEuMCZhbXA7cXVvdDsgZW5jb2Rpbmc9JmFtcDtxdW90O1VURi04JmFtcDtxdW90OyBzdGFuZGFsb25lPSZhbXA7cXVvdDtubyZhbXA7cXVvdDs/JmFtcDtndDsmIzEzOyAmYW1wO2x0OyEtLWZpbHRlcnMtLSZhbXA7Z3Q7JmFtcDtsdDt3aGVyZXMmYW1wO2d0OyYjMTM7ICZhbXA7bHQ7d2hlcmUgaXNFeGNsdWRlPSZhbXA7cXVvdDt0cnVlJmFtcDtxdW90OyBzY3JpcHQ9JmFtcDtxdW90O1dIRVJFIEJJX1NBTEVTX1BMQU5fTkVXLk1JU0NfREFURV9BVFRSSUJVVEVTLkFDQ09VTlRJTkdfREFZX0lOX1dFRUsgTkUgNiBPUiA3W1dIRVJFX1NFTUlDT0xPTl0mYW1wO3F1b3Q7LyZhbXA7Z3Q7JiMxMzsgJmFtcDtsdDsvd2hlcmVzJmFtcDtndDsmIzEzOyA8L1Byb3BlcnR5Pg0KICAgIDwvT2JqZWN0Pg0KICAgIDxPYmplY3Qgb2JqZWN0SWQ9IkdMT0JBTCI+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJTYW1wbGVEYXRhIiB0eXBlPSJqYXZhLmxhbmcuQm9vbGVhbiI+dHJ1ZTwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJHbG9iYWxSZWNvcmRMaW1pdCIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+NTAwPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9Ikdsb2JhbFJ1blJlY29yZExpbWl0IiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj4wPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9Ikdsb2JhbE9wdGltaXphdGlvbiIgdHlwZT0iamF2YS5sYW5nLkJvb2xlYW4iPnRydWU8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iZmllbGREaXNwbGF5TW9kZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+bGFiZWw8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0icHJlZml4RGlzcGxheU1vZGUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciLz4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9IkFjdGl2ZV9TdHlsZV9Vc2VyX3R5cGUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPnBvd2VyPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9IlNhdmVTdGFydFRvb2xJblR5cGUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPkNoYXJ0PC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9Ikdsb2JhbFZhbHVlc1BhZ2luZyIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+NDwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJGb2NleGVjUHJlZmVyZW5jZXMiIHR5cGU9Ik1hcCI+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5U2xpY2Vyc1RhYkVkaXRJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlTZXJpZXNUYWJJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImF1dG9EcmlsbFNjcmlwdCIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyIvPg0KICAgICAgICAgICAgPEVudHJ5IGtleT0icnVuT25TdGFydHVwRGVmZXJyZWRJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlFZGl0TW9kZUluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheUhvbWVUYWJJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9InRhcmdldEF1dG9MaW5rSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJtZXRhZGF0YV92aWV3cyIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+TWV0YURhdGFUcmVlLlZJRVdfRElNUzwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5RGF0YVRhYkluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iaW5mb0Fzc2lzdE1vZGVBbGxvd2VkSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJlbmFibGVBdXRvTGlua0luZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkZWZhdWx0X3ByZXZpZXdfcGFnZWxpbWl0IiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj41PC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlJbnNlcnRUYWJJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9InJ1bk9uU3RhcnR1cEluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0i
*-INTERNAL_COMMENT LINE#1$amF2YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5TGF5b3V0VGFiSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5SW50ZXJhY3RpdmVNb2RlSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj50cnVlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImVuYWJsZUluZm9NaW5pX1NhdmUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPnRydWU8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGVmYXVsdF9wcmV2aWV3X3BhZ2VsaW1pdF9sYXlvdXQiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPjE8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheVNsaWNlcnNUYWJJbnRlcmFjdGl2ZUluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkZWZhdWx0X2NvbXBvc2VfZm9ybWF0IiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5QREY8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheVJlc291cmNlc0ZpZWxkVGFiSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5Rm9ybWF0VGFiSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj50cnVlPC9FbnRyeT4NCiAgICAgICAgPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9ImNhc2NhZGVOYW1lcyIgdHlwZT0iTWFwIi8+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJNYXN0ZXJfRmlsZXMiIHR5cGU9IlNldCI+DQogICAgICAgICAgICA8RW50cnkgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+QklfU0FMRVNfUExBTl9ORVc8L0VudHJ5Pg0KICAgICAgICA8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0ibWV0YWRhdGFWaWV3QXMiIHR5cGU9Ik1hcCI+DQogICAgICAgICAgICA8RW50cnkga2V5PSJCSV9TQUxFU19QTEFOX05FVyIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+TWV0YURhdGFUcmVlLlZJRVdfRElNUzwvRW50cnk+DQogICAgICAgIDwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJTbGljZXJHdWlJc2xhbmQiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmV5SmlRWFYwYjFCeVpYWnBaWGNpT21aaGJITmxMQ0ppVDNCMGFXOXVjMGR5YjNWd1ZtbHphV0pzWlNJNmRISjFaU3dpWWxKbFkweHBiV2wwUjNKdmRYQldhWE5wWW14bElqcDBjblZsTENKaVVISmxkbWxsZDBOdmJuUnliMnhXYVhOcFlteGxJanAwY25WbExDSmlVblZ1ZEdsdFpVTnZiblJ5YjJ4V2FYTnBZbXhsSWpwMGNuVmxmUT09PC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9IlNMSUNFUl9JTkZPUk1BVElPTiIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+UEQ5NGJXd2dkbVZ5YzJsdmJqMGlNUzR3SWlCbGJtTnZaR2x1WnowaVZWUkdMVGdpSUhOMFlXNWtZV3h2Ym1VOUltNXZJajgrUENFdExVTlBUVkJNUlZSRlgxTk1TVU5GVWw5SFVrOVZVQzB0UGp4VFRFbERSVkpmUjFKUFZWQStQRWRTVDFWUUlHZHliM1Z3VG5WdFltVnlQU0l3SWlCemJHbGpaWEpIY205MWNFeGhZbVZzUFNKSGNtOTFjQ0F4SWlCemJHbGpaWEpIY205MWNFOXlaR1Z5UFNJd0lpQnpiR2xqWlhKSGNtOTFjRk5wZW1VOUlqQWlJSE5zYVdObGNrZHliM1Z3YUdsa1pUMGlabUZzYzJVaUx6NDhMMU5NU1VORlVsOUhVazlWVUQ0PTwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJlbmFibGVQcmV2aWV3IiB0eXBlPSJqYXZhLmxhbmcuQm9vbGVhbiI+dHJ1ZTwvUHJvcGVydHk+DQogICAgPC9PYmplY3Q+DQo8L1Jvb3Q+DQo=
-*Do not delete or modify the comments above
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET HTMLENCODE=OFF
SET ARGRAPHENGINE=JSCHART
SET EMBEDHEADING=ON
SET GRAPHDEFAULT=OFF

-*COMPONENT=Define_BI_SALES_PLAN_NEW
DEFINE FILE BI_SALES_PLAN_NEW
Gross_Sales/D12MC=BI_SALES_PLAN_NEW.DOLLAR_MEASURES.ACTUAL_GROSS_SALES_AMOUNT + BI_SALES_PLAN_NEW.DOLLAR_MEASURES.ACTUAL_DISCOUNT_AMOUNT ;
END
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';

-DEFAULTH &WF_TITLE='WebFOCUS Report';
GRAPH FILE BI_SALES_PLAN_NEW
-* Created by Info Assist for Graph
SUM BI_SALES_PLAN_NEW.DOLLAR_MEASURES.AOP_REVENUE AS 'AOP Rev Tgt'
Gross_Sales AS 'Net Sales'
COMPUTE CTR/I4=LAST CTR + 1 ; NOPRINT
COMPUTE LAG_1/D12.2=LAST Gross_Sales ; NOPRINT
COMPUTE Lag_2/D12.2=LAST LAG_1 ; NOPRINT
COMPUTE LAG_3/D12.2=LAST Lag_2 ; NOPRINT
COMPUTE LAG_4/D12.2=LAST LAG_3 ; NOPRINT
COMPUTE AVER/D12.2=IF CTR LT 4 THEN 0 ELSE ( Gross_Sales + LAG_1 + Lag_2 + LAG_3 + LAG_4 )/5 ; AS '5 Day Avg'
BY BI_SALES_PLAN_NEW.MISC_DATE_ATTRIBUTES.FULL_DATE
WHERE BI_SALES_PLAN_NEW.CURRENT.CURRENT_MONTH EQ 1 OR 2;
WHERE BI_SALES_PLAN_NEW.PRODUCT_SUMMARY.BUSINESS_UNIT_CODE EQ 'C';
WHERE BI_SALES_PLAN_NEW.PART_ATTRIBUTES.PRODUCT_FORM_CODE EQ 'R';
HEADING
"FY 2020 Daily Sales Trend"
" "
" "
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET AUTOFIT ON
ON GRAPH SET GRWIDTH 1
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS &WF_STYLE_WIDTH
ON GRAPH SET VAXIS &WF_STYLE_HEIGHT
ON GRAPH SET LOOKGRAPH BAR
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setPlace(true);
setCurveFitEquationDisplay(false);

*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, ORIENTATION=LANDSCAPE, $
TYPE=HEADING, JUSTIFY=CENTER, $
TYPE=HEADING, LINE=1, ITEM=1, OBJECT=TEXT, FONT='Trebuchet MS', SIZE=12, COLOR=RGB(66 70 73), STYLE=BOLD, $
TYPE=HEADING, LINE=2, ITEM=1, OBJECT=FIELD, FONT='Trebuchet MS', SIZE=12, COLOR=RGB(66 70 73), STYLE=BOLD, $
TYPE=HEADING, LINE=2, ITEM=1, OBJECT=TEXT, FONT='Trebuchet MS', SIZE=12, COLOR=RGB(66 70 73), STYLE=BOLD, $
TYPE=HEADING, LINE=3, ITEM=1, OBJECT=TEXT, SIZE=12, STYLE=NORMAL, $
TYPE=DATA, COLUMN=N1, BUCKET=x-axis, $
TYPE=DATA, COLUMN=N2, BUCKET=y-axis, $
TYPE=DATA, COLUMN=N3, BUCKET=y-axis, $
TYPE=DATA, COLUMN=N4, BUCKET=y-axis, $
TYPE=DATA, COLUMN=N5, BUCKET=y-axis, $
TYPE=DATA, COLUMN=N6, BUCKET=y-axis, $
TYPE=DATA, COLUMN=N7, BUCKET=y-axis, $
TYPE=DATA, COLUMN=N8, BUCKET=y-axis, $
TYPE=DATA, COLUMN=N9, BUCKET=y-axis, $
*GRAPH_SCRIPT

setUseSeriesShapes(true);
setReportParsingErrors(false);
setSelectionEnableMove(false);
setReferenceTextPosition(getReferenceLineTextY1(0),1);
setDisplay(getReferenceLineY1(0),true);
setBorderColor(getReferenceLineY1(0),new Color(0,0,0));
setFillColor(getReferenceLineTextY1(0),new Color(0,0,0));
setLineWidth(getReferenceLineY1(0),1);
setLineBasicStrokeType(getReferenceLineY1(0),0);
setTextString(getReferenceLineTextY1(0),"5 Day Avg");
setDisplay(getReferenceLineTextY1(0),true);
setLegendPosition(2);
setDisplay(getLegendArea(),true);
setSeriesType(0,2);
setSeriesType(1,1);
setDataTextPosition(1);
setTextFormatPreset(getDataText(),8);
setDisplay(getDataText(0), false);
setDisplay(getDataText(1), true);
setDataTextDisplay(true);
setTextString(getO1Title(),"Accounting Day");
setDisplay(getO1Title(),true);
setTextString(getY1Title(),"Net Sales");
setDisplay(getY1Title(),true);
setFillColor(getSeries(0),new Color(0,0,0));
setFillColor(getSeries(1),new Color(0,128,255));
setFillType(getSeries(1),2);
setGradientDirection(getSeries(1),3);
setGradientPinLeftColor1(getSeries(1),new Color(0,128,255));
setGradientPinRightColor1(getSeries(1),new Color(0,128,255));
setGradientPinPosition0(getSeries(1),0.0);
setGradientPinPosition1(getSeries(1),1.0);
setTransparentBorderColor(getSeries(1), true);
setTransparentBorderColor(getSeries(0), true);
setSeriesType(19,2);
setSeriesType(18,2);
setSeriesType(17,2);
setSeriesType(16,2);
setSeriesType(15,2);
setSeriesType(14,2);
setSeriesType(13,2);
setSeriesType(12,2);
setSeriesType(11,2);
setSeriesType(10,2);
setSeriesType(9,2);
setSeriesType(8,2);
setSeriesType(7,2);
setSeriesType(6,2);
setSeriesType(3,2);
setSeriesType(2,2);
setDisplay(getDataText(2), false);
setPieFeelerTextDisplay(0);
setFillMissingData(2);
setFillColor(getSeries(2),new Color(0,128,64));
setCurveFitType(1,0);
*GRAPH_JS_FINAL
"xaxis": {
"title": {
"visible": true,
"text": "Accounting Day"
}
},
"pieProperties": {
"holeSize": "0%"
},
"yaxis": {
"title": {
"visible": true,
"text": "Net Sales"
}
}

*END
ENDSTYLE
END

-RUN

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

Noleary

You need to create a COMPUTE with some boolean logic. Something like this

COMPUTE FRIDAY_SALES/D12.2 = IF DAY EQ 'Friday' OR 'Saturday' OR 'Sunday' THEN SALES ELSE 0;

Hi FP,

I don't think that solution will work in my situation. I have a bar chart that shows each date of the month across the bottom with the sales of each day as the bar. I want to take the Saturday and Sunday values and roll them into the Friday values. So for the month of March it should show 3/2 (Monday), 3/3 (Tuesday), 3/4 (Wednesday), 3/5 (Thursday), 3/6 (Friday+Saturday+Sunday), 3/9 (Monday), 3/10 (Tuesday), 3/11 (Wednesday), 3/12(Thursday), 3/13 (Friday+Saturday+Sunday), etc. I don't think what you suggested would produce what I need.

So redefine the value of BI_SALES_PLAN_NEW.MISC_DATE_ATTRIBUTES.FULL_DATE to the previous Friday if the date is a Saturday or Sunday.

Then sort by that field instead.

T

quote:

Originally posted by Tony A:
So redefine the value of BI_SALES_PLAN_NEW.MISC_DATE_ATTRIBUTES.FULL_DATE to the previous Friday if the date is a Saturday or Sunday.

Then sort by that field instead.

T

That worked! Thanks for the suggestion Tony

You're welcome

Now go to your first post and edit it (lower right of the post - folder and pencil with eraser icon).

Change the title of the post to be prefixed with [SOLVED].

That way, the moderators can cast a quick eye over what needs to be responded to (or not )

T