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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.
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
-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);
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.
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.