[SOLVED] In Infoassist can you make column percents based on row total
So here is my question.. if you are using the GUI ( only ) in infoassist, and you have data across and by with counts as sum. Can you get the across columns to show a percent of the row total ? Or are you limited to changeing the code to do that ?
I tried the "percent of count" aggregation function, but that seems to do a percent of the number I am working with not the row total ( so not clear on use of that at all always = 100%) .
Thanks, TaraThis message has been edited. Last edited by: <Kathryn Henning>,
8002 Windows
November 22, 2013, 03:51 PM
VLozovsky
Tara O,
Would it be possible for you to upload a an example of what you are trying to achieve? I think you may be trying to use WITHIN capability which manipulates a display field values as they are aggregated within a sort group, rather than a report group. This feature is available in in InfoAssist as of Release 8.0.04
Thanks, Vicky Lozovsky
November 22, 2013, 04:38 PM
GCohen
Why can't you use the RPCT. prefix e.g.
SUM SALES AND RPCT.SALES ACROSS PRODUCT ROW-TOTAL BY STATE
Release 7.6.9 Windows HTML
November 22, 2013, 04:44 PM
Tara O
Hi Vicki - Sorry I am not sure how to attach a screen capture. But my table is like this:
Race CA AA TOTAL Location ITEMTOT PCT ITEMTOT PCT ITEMTOT PCT COMMC 17 17 23 23 40 40
What I want is to use the GUI to get a Percent of the total under PCT.
My code is as follows: -*COMPONENT=Join_J001 JOIN IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.PATIENTID IN IBI_VIEW_VISIT_OUTPT TO MULTIPLE PBR_PATIENT_VW.PBR_PATIENT_VW.PATIENTID IN PBR_PATIENT_VW TAG J001 AS J001 END -*COMPONENT=Join_J002 JOIN J001.PBR_PATIENT_VW.RACEID IN IBI_VIEW_VISIT_OUTPT TO MULTIPLE IBI_TABLE_RACE_DESC.RACE_DESC.RACEID IN IBI_TABLE_RACE_DESC TAG J002 AS J002 END -DEFAULTH &WF_SUMMARY='Summary'; -DEFAULTH &WF_TITLE='WebFOCUS Report'; TABLE FILE IBI_VIEW_VISIT_OUTPT SUM CNT.DST.IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.APPOINTMENTID AS 'ITEMTOT' COMPUTE pct/I5=CNT.DST.IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.APPOINTMENTID ; BY IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.LOCATIONNAME ACROSS J002.RACE_DESC.NAME AS 'RACE' WHERE IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.STS EQ 'ATTENDED' OR 'BOOKED'; WHERE IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.APPTDT GE '&MINAPPTDT.(FIND IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.APPTDT IN IBI_VIEW_VISIT_OUTPT).Report Start Date YYYY-MM-DD:.'; WHERE IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.APPTDT LE '&MAXAPPTDT.(FIND IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.APPTDT IN IBI_VIEW_VISIT_OUTPT).Report End Date YYYY-MM-DD:.'; WHERE IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.LOCATIONNAME EQ &LOCATIONNAME.(OR(FIND IBI_VIEW_VISIT_OUTPT.VISIT_OUT_PAT_VW.LOCATIONNAME IN IBI_VIEW_VISIT_OUTPT)).LOCATIONNAME:.; ON TABLE SUBHEAD " PBR Attended / Booked Appointments " "&MINAPPTDT through &MAXAPPTDT" "for &LOCATIONNAME " ON TABLE PCHOLD FORMAT HTML ON TABLE NOTOTAL ON TABLE ROW-TOTAL ON TABLE SET CACHELINES 100 ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET SQUEEZE ON ON TABLE SET HTMLCSS ON ON TABLE SET HTMLENCODE ON ON TABLE SET EMPTYREPORT ON ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$ TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, HFREEZE=OFF, $ TYPE=TABHEADING, LINE=1, JUSTIFY=CENTER, $ TYPE=TABHEADING, LINE=1, ITEM=1, OBJECT=TEXT, SIZE=14, STYLE=BOLD+ITALIC, $ TYPE=TABHEADING, LINE=2, JUSTIFY=CENTER, $ TYPE=TABHEADING, LINE=2, ITEM=1, OBJECT=TEXT, SIZE=14, STYLE=BOLD+ITALIC, $ TYPE=TABHEADING, LINE=3, JUSTIFY=CENTER, $ TYPE=TABHEADING, LINE=3, ITEM=1, OBJECT=TEXT, SIZE=14, STYLE=BOLD+ITALIC, $ ENDSTYLE END
Thanks, Tara
8002 Windows
November 23, 2013, 01:11 PM
njsden
quote:
I am not sure how to attach a screen capture
Just take a screenshot using Alt-PrintScr or any other software you may have to do so, save the image in .png format for instance and upload it to www.tinypic.com which will then provide you with a URL you can just include here.
can anyone see those? I cant ,but my company blocks images alot so it may just be me.
8002 Windows
December 04, 2013, 02:57 PM
Tara O
Sorry I cant get the image to come up .. but all I want is... imagine a row of data with : -each month for the across (headers) and -counts of the number of visits for each month with a total number of visits for the year (data) . I want to add, next to the count of visits for each month, the % of total year visits that represents. So January 15% of our patients came in .... Conceptually , that is what I am going for ...any ideas on how to do this from Infoassist?
8002 Windows
December 04, 2013, 04:42 PM
njsden
Do you mean something like the following?
DEFINE FILE GGSALES
VISIT_CNT/P6 = 1; <-- Counter
MTH/M = DATE;
MTH_NAME/Mt = MTH;
END
TABLE FILE GGSALES
SUM
VISIT_CNT AS 'Visits'
RPCT.VISIT_CNT/P6.1% AS '%'
BY REGION
ACROSS MTH NOPRINT
ACROSS MTH_NAME AS 'Visits in 1997'
WHERE DATE FROM 19970101 TO 19971231
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOPAGE
END
If that looks somewhat similar to what you need, I'm confident you can do that in InfoAssist. All that's needed would be to DEFINE a counter field displaying both the SUM. and RPCT. of such counter in a SUM report.
Originally posted by GCohen: Why can't you use the RPCT. prefix e.g.
Sorry, I just noticed this. My previous suggestion is no different than what GCohen had already mentioned to you days ago which I assume you already tried but for some reason didn't work for you.
Unfortunately your description of what your report needs seems like the perfect description for what RPCT. does by default so I don't know how else to help
Yes that is exactly what I need..but I want to do it from the GUI ( however your code looks better than what I came up with from a code change standpoint .. so I might plifer off of it )
8002 Windows
December 05, 2013, 04:59 AM
Twanette
Hi, I think this would be the process to follow from the GUI (I'm testing on WF 8.0.05):
1) In your report, add the VISIT_CNT column twice i.e. two VISIT_CNT columns being summed 2) Click on the second instance of the VISIT_CNT column - and the right-click to get your context menu of options for that column 3) Select the "More" option 4) Select "Aggregation Functions" 5) Select the "Row Percentage" option - typically somewhere near the bottom of the list