[CLOSED] CNT.DST in Advanced Graph Assistant

July 07, 2011, 10:49 AM

I have built a graph that references a FOCUS db object. This object provides a distinct count of transactions by day. In the graph I want to display dintinct counts for a range of dates (parm) and instead of returning a distinct count of the transactions it is simply summing them based on the date range parm. Instead of SUM. I tried CNT.DST and it won't work.

I neglected to mention that this is a gauge graph (tachometer) and that may be my issue (multiple data points without a table request). I'm hoping there's a way around this.
Any ideas?

 -* File graph2_transcounts.fex

-****************************************
-* kadkins 05/09/2011                   *
-****************************************

-* This graph displays the number of stores with a transaction within the last 7/10/14 days.

-* TRANSACTIONTYPE codes:
-*4 ? is a Return which will be a negative value.
-*5 ? is a Sale which will be a positive value.
-*16 ? is a Post Void of a Return which will be a positive value.  i.e. (same as a sale)
-*17 ? is a Post Void of a Sale which will be a negative value.  i.e. (same as a return)

-*INCLUDE setuser.fex

DEFINE FILE TRANSHISTORY
d_sales/P22.2=
IF TRANSHISTORY.TRANSHISTORY.TRANSACTIONTYPE EQ '4' OR '17'
THEN -1*TRANSHISTORY.TRANSHISTORY.TRANSACTIONAMT
ELSE TRANSHISTORY.TRANSHISTORY.TRANSACTIONAMT;
d_transdate/YYMD=HDATE(TRANSHISTORY.TRANSHISTORY.TRANSACTIONDATE, 'YYMD');
d_today/YYMD=&YYMD;
d_trans1/D12.2=DATEADD(d_today,'D',-1);
d_trans3/D12.2=DATEADD(d_today,'D',-3);
d_trans7/D12.2=DATEADD(d_today,'D',-7);
d_trans8/D12.2=DATEADD(d_today,'D',-8);
d_trans10/D12.2=DATEADD(d_today,'D',-10);
d_trans14/D12.2=DATEADD(d_today,'D',-14);
END
TABLE FILE TRANSHISTORY
SUM
     'CNT.DST.TRANSHISTORY.TRANSHISTORY.STORENUMBER'
 AS 'COUNT,DISTINCT,TRANSHISTORY.TRANSHISTORY.STORENUMBER'
BY 'TRANSHISTORY.TRANSHISTORY.d_transdate'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_DISTSTORETRANS FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
DEFINE FILE HOLD_DISTSTORETRANS
d_transdate/YYMD=HDATE(TRANSHISTORY.TRANSHISTORY.TRANSACTIONDATE, 'YYMD');
d_today/YYMD=&YYMD;
d_trans1/D12.2=DATEADD(d_today,'D',-1);
d_trans3/D12.2=DATEADD(d_today,'D',-3);
d_trans7/D12.2=DATEADD(d_today,'D',-7);
d_trans8/D12.2=DATEADD(d_today,'D',-8);
d_trans10/D12.2=DATEADD(d_today,'D',-10);
d_trans14/D12.2=DATEADD(d_today,'D',-14);
END
GRAPH FILE HOLD_DISTSTORETRANS
-* Created by Advanced Graph Assistant
SUM HOLD_DISTSTORETRANS.SEG01.COUNT
WHERE HOLD_DISTSTORETRANS.SEG01.d_transdate GT &d_transdate;
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 360
ON GRAPH SET VAXIS 360
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH GAUGE1
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 0
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBITrueColors.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setTransparentBorderColor(getChartBackground(),true);
setPlace(true);
setDisplay(getGaugeTitle(),false);
setTextString(getTitle(),"# Stores With Transactions ");
setDisplay(getTitle(),true);
setTextString(getSubtitle(),"By Date Range");
setDisplay(getSubtitle(),true);
setFillColor(getGaugeValue(),new Color(21,21,21));
setFontStyle(getGaugeValue(),3);
setFontStyle(getY1Label(),1);
setGaugeBackgroundStyle(1);
setGaugeBandMin(getGaugeBand4(),0.0);
setGaugeBandMax(getGaugeBand4(),0.0);
setGaugeBandMin(getGaugeBand5(),0.0);
setGaugeBandMax(getGaugeBand5(),0.0);
setGaugeBandMin(getGaugeBand3(),1500.0);
setGaugeBandMax(getGaugeBand3(),4500.0);
setGaugeBandMin(getGaugeBand2(),4500.0);
setGaugeBandMax(getGaugeBand1(),1500.0);
setGaugeBandMin(getGaugeBand1(),0.0);
setGaugeBandMax(getGaugeBand2(),10000.0);
setScaleMaxAuto(getY1Axis(),false);
setScaleMax(getY1Axis(),10000.0);
setBorderColor(getGaugeBand1(),new Color(255,0,0));
setFillColor(getGaugeBand1(),new Color(255,0,0));
setBorderColor(getGaugeBand2(),new Color(0,128,0));
setFillColor(getGaugeBand2(),new Color(0,128,0));
setBorderColor(getGaugeBand3(),new Color(255,255,0));
setFillColor(getGaugeBand3(),new Color(255,255,0));
setBorderColor(getGaugeBand4(),new Color(255,255,0));
setFillColor(getGaugeBand4(),new Color(255,255,0));
setBorderColor(getGaugeBand5(),new Color(255,255,0));
setFillColor(getGaugeBand5(),new Color(255,255,0));
setDisplay(getGaugeValue(),true);
setGaugeShowZeroLabel(true);
setTextFormatPreset(getY1Label(),1);
setFontSizeAbsolute(getTitle(),true);
setFontSizeInPoints(getTitle(),10);
setPlaceResize(getTitle(),0);
setFontSizeAbsolute(getSubtitle(),true);
setFontSizeInPoints(getSubtitle(),10);
setPlaceResize(getSubtitle(),0);
setFontSizeAbsolute(getY1Label(), true);
setFontSizeInPoints(getY1Label(), 8);
setPlaceResize(getY1Label(), 0);
ENDSTYLE
END

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