Focal Point
[CLOSED] InfoAssist - day / month / year

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1617070196

January 28, 2019, 08:40 AM
Ronibi
[CLOSED] InfoAssist - day / month / year
Hai

We already made some dashboards using widgets made in InfoAssist.
We want to do as much without using any hand-coding.

Now we want to make a dashboard with InfoAssist and Page Designer containing six widgets. On the top of the dashboard there should be three possible choices:
- day, widgets aggregated per day, showing the last 15 days
- week, widgets aggregated per week, showing the last 15 weeks
- month, widgets aggregated per month, showing the last 15 months

Is it possible to make this just with IA and Page Designer functionality, so without any HTML?

Regards
Ron

P.S. Always interested in amazing things that can be done with InfoAssist in combination with Page Designer. Beautiful examples are hard to find on internet

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
January 28, 2019, 10:20 AM
BabakNYC
You can DEFINE a field to be set to &YYMD first. Then create the additional 3 dates based on that field. Finally, use these defined fields in a WHERE test so you can limit the output to the dates you want.

However, if this is a commonly needed set of dates, why not define them in your synonyms as filters so all you have to do is drag and drop them into your charts/reports. I always make sure I have Yesterday, Last Week, Last Month, Last Quarter Last Year, et cetera ready to be used by everyone.


WebFOCUS 8206, Unix, Windows
January 28, 2019, 10:35 AM
DWaybright
We developed a single row table that has dates that are commonly used as filters in reports. We join the main table we are using to this table in our report and then we have all the dates (beginning of year, beginning of month, last month, etc) to use in the filters or headers of the report. Since the most commonly used table for reports has many years worth of data, it helps with runtimes as you can filter the data in the join (just pull this year's data, for example).


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
January 28, 2019, 10:45 AM
Ronibi
Ok BabakNYC,

So I update my master. Define:
- field A always containing the current date
- field B = field A - 15 days
- field C = field A - 15 weeks
- field D = field A - 15 months

I make a report and define three filters:
- filter X (day) = date of transaction > field B --> then I have the transactions of the last 15 days, but NOT aggregated by day
- filter Y (week) = date of transaction > field C --> then I have the transactions of the last 15 weeks, but NOT aggregated by week
- filter Z (month) = date of transaction > field D --> then I have the transactions of the last 15 months, but NOT aggregated by month

How do I get the correct aggregations? Is all this possible in one report?

regards
Ron

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


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
January 28, 2019, 01:32 PM
BabakNYC
I'm not sure if I'm reading this right. Just to be sure, your filter has to bring back only dates that are between FILTERX and FIELDB.


WebFOCUS 8206, Unix, Windows
January 28, 2019, 02:42 PM
Ronibi
I think we misunderstand each other BabakNYC.
I have a dashboard with six widgets and three filters or buttons. The filters are 'By day', 'By week', 'By month'.

Initially it shows the numbers of all the widgets aggregated 'By day'.
When I push on 'By week' all the widgets should be updated and show the numbers aggregated by weeks for the last 15 weeks.
When I then push on 'By month' all the widgets should be updated and show the numbers aggregated by months for the last 15 months.

So suppose I use a bar chart. Everytime the chart has 15 bars, but everytime it is aggregated on another level.


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
January 28, 2019, 02:43 PM
Ronibi
quote:
Originally posted by DWaybright:
We developed a single row table that has dates that are commonly used as filters in reports. We join the main table we are using to this table in our report and then we have all the dates (beginning of year, beginning of month, last month, etc) to use in the filters or headers of the report. Since the most commonly used table for reports has many years worth of data, it helps with runtimes as you can filter the data in the join (just pull this year's data, for example).


I suppose the single row contains as starting point always the processing date?


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
January 29, 2019, 02:29 PM
BabakNYC
I didn't have time to attempt this in InfoAssist but below is a starting point for what you're trying to do. There might also be much better ways of doing this. I've a TABLE request that captures the values you'll need for the GRAPH request that follows. In this example I first find the records I need using a parameterized BY and put the answer set in a HOLD file. Then I chart the data in the HOLD file. You'll have to add your FROM-TO date logic. As it is, it'll just assume the last 5 records in the data are what you want. You can change that to 15 for your specific example.

  

ENGINE INT CACHE SET ON
SET ARGRAPHENGINE=JSCHART
SET GRAPHDEFAULT=OFF
TABLE FILE retail/wf_retail
SUM WF_RETAIL.WF_RETAIL_SALES.REVENUE_US
BY HIGHEST 5 &XAXIS.( <Sale Year,WF_RETAIL.WF_RETAIL_TIME_SALES.TIME_DATE_YEAR_COMPONENT>,<Sale Year/Month,WF_RETAIL.WF_RETAIL_TIME_SALES.TIME_DATE_MONTH_COMPONENT>,<Sale Day,WF_RETAIL.WF_RETAIL_TIME_SALES.TIME_DATE_DAY_COMPONENT>).Select.
ON TABLE HOLD
END
-RUN
GRAPH FILE HOLD
SUM REVENUE_US AS 'Revenue'
BY E01 AS 'Sale Date'

ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET GRWIDTH 1
ON GRAPH SET AUTOFIT ON
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/ibi_themes/Warm.sty,$
TYPE=DATA, COLUMN=N1, BUCKET=x-axis, $
TYPE=DATA, COLUMN=N2, BUCKET=y-axis, $
*GRAPH_SCRIPT

setReportParsingErrors(false);
setSelectionEnableMove(false);

*END
ENDSTYLE
END

-RUN



WebFOCUS 8206, Unix, Windows
January 29, 2019, 02:57 PM
Ronibi
Ok, thnx.
Hope you don't bother but I would like to know if this is possible in InfoAssist without doing any coding. So just clicking.
Although I don't know if a paramterized BY is possible in InfoAssist.
I will give it a try one of these days.

Regards
Ron


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
February 02, 2019, 05:20 AM
Ronibi
Tried in InfoAssist but I think it is not possible without coding.
A pity.

Thnx anyway


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC