Average in Crossfilter and DC.js

0
votes

In my application, I have two charts and one table. The charts names are sellerChart and dateUnitPriceChart and table name is datatable.For sellerChart, I create dimension like this and then draw a chart : 

var sellerDimension = data.dimension(function (d) {
        return d.slr_Name;
    });
var sellerGroup = sellerDimension.group().reduceSum(function (d) {
        return d.fdl_Num;
    });

As you see is use reduceSum and this is what I want but for another chart, I want to use average but currently, it uses reduceSum like this 

var dateDimension = data.dimension(function (d) {
        return d.fct_Date;
    });
var unitPriceGroup = dateDimension.group().reduceSum(function
        (d) {
        return d.fdl_UniyPrice;
    });

for showing average I try to use reductio like this : 

var unitPriceGroup = dateDimension.group()
    var reducer = reductio()
    .count(true)
    .sum(function (d) {
        return d.fdl_UniyPrice;
    })
    .avg(true);
reducer(unitPriceGroup);

Now I have averaged in reducer but the chart show only X and Y axis without any information, so my question is how to show average based on the average of fdl_UniyPrice. I create a JS Fiddle, my codes start from line 43 and my data start on line 42

1
d3.jsdc.jscrossfilterreductio
Could u plz provide a data specific jsfiddle. i am confused with the current jsfiddle data and the chart what you are explained? – Aravind Cheekkallur
@AravindCheekkallur thanks for your attention, I update my fiddle, previous fiddle show wrong js. – MBehtemam
Do u wanna plot avg of ''fdl_UniyPrice" based on date? I mean what would u expect in x axis? – Aravind Cheekkallur
@AravindCheekkallur yes I need avg of fdl_UiyPrice base of date only for dateUnitPriceChart.For the first chart, I use sum to show a count of sells for example but for the second chart I need to avg of the price of a date, for example, avg of the price of apples that buy in Sunday not sum of all price of apples on Sunday. – MBehtemam
@AravindCheekkallur showing average of fdl_Uniyprice in each date – MBehtemam

1 Answers

1
votes

While looking at your data and based on your explanation, avg calculation can be achieved using custom reduce function. By using the dateDimension to create grouping function to get the average. So regardless of the slr_Name and mrq_Requester_dep_title, It will create group for each date with sum up the fdl_UniyPrice. 

var dateDimension = data.dimension(function (d) {
        return d.fct_Date;
    });
var unitPriceGroup = dateDimension.group().reduce(
        //return d.fdl_UniyPrice;
        //add
    function (p, v) {
        ++p.count;
        p.total += v.fdl_UniyPrice;
        if (p.count == 0) {
            p.average = 0;
        } else {
            p.average = p.total / p.count;
        }
        return p;
    },
    // remove
    function (p, v) {
        --p.count;
        p.total -= v.fdl_UniyPrice;
        if (p.count == 0) {
            p.average = 0;
        } else {
            p.average = p.total / p.count;
        }
        return p;
    },
    // initial
    function () {
        return {
            count: 0,
            total: 0,
            average: 0
        };
    }
);


var myData = [{"slr_Name":"handStore","fct_Date":"10/31/2016","fdl_UniyPrice":40000,"fdl_TotalPrice":2400000,"ShamsiDate":"1395/08/10","mrq_No":"335","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"boss office","fdl_Num":60},{"slr_Name":"Sh Store","fct_Date":"12/12/2016","fdl_UniyPrice":50000,"fdl_TotalPrice":60000,"ShamsiDate":"1395/09/22","mrq_No":"357","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Start Up","fdl_Num":1.2},{"slr_Name":"handStore","fct_Date":"12/18/2016","fdl_UniyPrice":44000,"fdl_TotalPrice":96800,"ShamsiDate":"1395/09/28","mrq_No":"344","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Select Office","fdl_Num":2.2},{"slr_Name":"handStore","fct_Date":"12/18/2016","fdl_UniyPrice":40000,"fdl_TotalPrice":1240000,"ShamsiDate":"1395/09/28","mrq_No":"326","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"U Office","fdl_Num":31},{"slr_Name":"handStore","fct_Date":"12/20/2016","fdl_UniyPrice":42000,"fdl_TotalPrice":2142000,"ShamsiDate":"1395/09/30","mrq_No":"340","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"MH","fdl_Num":51},{"slr_Name":"N Brothers","fct_Date":"12/21/2016","fdl_UniyPrice":38000,"fdl_TotalPrice":1140000,"ShamsiDate":"1395/10/01","mrq_No":"440","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Start Up","fdl_Num":30},{"slr_Name":"N Brothers","fct_Date":"12/21/2016","fdl_UniyPrice":38000,"fdl_TotalPrice":2432000,"ShamsiDate":"1395/10/01","mrq_No":"423","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"U Office","fdl_Num":64},{"slr_Name":"N Brothers","fct_Date":"12/26/2016","fdl_UniyPrice":38000,"fdl_TotalPrice":1710000,"ShamsiDate":"1395/10/06","mrq_No":"420","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Managements","fdl_Num":45},{"slr_Name":"handStore","fct_Date":"12/27/2016","fdl_UniyPrice":44000,"fdl_TotalPrice":1320000,"ShamsiDate":"1395/10/07","mrq_No":"345","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Finance","fdl_Num":30},{"slr_Name":"handStore","fct_Date":"12/27/2016","fdl_UniyPrice":40000,"fdl_TotalPrice":280000,"ShamsiDate":"1395/10/07","mrq_No":"337","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Manage and create","fdl_Num":7},{"slr_Name":"Store 2","fct_Date":"12/28/2016","fdl_UniyPrice":38000,"fdl_TotalPrice":874000,"ShamsiDate":"1395/10/08","mrq_No":"424","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Start Up","fdl_Num":23},{"slr_Name":"handStore","fct_Date":"1/8/2017","fdl_UniyPrice":44000,"fdl_TotalPrice":264000,"ShamsiDate":"1395/10/19","mrq_No":"425","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"human resource","fdl_Num":6},{"slr_Name":"handStore","fct_Date":"2/21/2017","fdl_UniyPrice":47000,"fdl_TotalPrice":117500,"ShamsiDate":"1395/12/03","mrq_No":"553","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Doctors","fdl_Num":2.5},{"slr_Name":"handStore","fct_Date":"3/3/2017","fdl_UniyPrice":55000,"fdl_TotalPrice":3300000,"ShamsiDate":"1395/12/13","mrq_No":"577","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Human2","fdl_Num":60},{"slr_Name":"handStore","fct_Date":"3/26/2017","fdl_UniyPrice":70000,"fdl_TotalPrice":252000,"ShamsiDate":"1396/01/06","mrq_No":"1","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"boss office","fdl_Num":3.6},{"slr_Name":"B Store","fct_Date":"5/2/2017","fdl_UniyPrice":68000,"fdl_TotalPrice":89760,"ShamsiDate":"1396/02/12","mrq_No":"324","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"boss office","fdl_Num":1.32},{"slr_Name":"Y Store","fct_Date":"5/7/2017","fdl_UniyPrice":40000,"fdl_TotalPrice":1360000,"ShamsiDate":"1396/02/17","mrq_No":"303","drq_SaleSplName":"AZ","mrq_Requester_dep_title":"D & F Manage","fdl_Num":34},{"slr_Name":"handStore","fct_Date":"5/9/2017","fdl_UniyPrice":53600,"fdl_TotalPrice":268000,"ShamsiDate":"1396/02/19","mrq_No":"866","drq_SaleSplName":"AZ","mrq_Requester_dep_title":"manage office","fdl_Num":5},{"slr_Name":"Y Store","fct_Date":"5/14/2017","fdl_UniyPrice":70000,"fdl_TotalPrice":1062600,"ShamsiDate":"1396/02/24","mrq_No":"445","drq_SaleSplName":"ES Ja","mrq_Requester_dep_title":"Wom","fdl_Num":15.18},{"slr_Name":"handStore","fct_Date":"5/24/2017","fdl_UniyPrice":50000,"fdl_TotalPrice":300000,"ShamsiDate":"1396/03/03","mrq_No":"866","drq_SaleSplName":"AZ","mrq_Requester_dep_title":"manage office","fdl_Num":6},{"slr_Name":"Y Store","fct_Date":"5/29/2017","fdl_UniyPrice":50000,"fdl_TotalPrice":920000,"ShamsiDate":"1396/03/08","mrq_No":"866","drq_SaleSplName":"AZ","mrq_Requester_dep_title":"manage office","fdl_Num":18.4},{"slr_Name":"Y Store","fct_Date":"8/19/2017","fdl_UniyPrice":37000,"fdl_TotalPrice":370000,"ShamsiDate":"1396/05/28","mrq_No":"2496","drq_SaleSplName":"AZ","mrq_Requester_dep_title":"D & F Manage","fdl_Num":10},{"slr_Name":"Sh Store","fct_Date":"9/4/2017","fdl_UniyPrice":65000,"fdl_TotalPrice":195000,"ShamsiDate":"1396/06/13","mrq_No":"2885","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"D Center","fdl_Num":3},{"slr_Name":"Y Store","fct_Date":"10/9/2017","fdl_UniyPrice":40000,"fdl_TotalPrice":800000,"ShamsiDate":"1396/07/17","mrq_No":"3753","drq_SaleSplName":"AZ","mrq_Requester_dep_title":"D & F Manage","fdl_Num":20},{"slr_Name":"handStore","fct_Date":"10/19/2017","fdl_UniyPrice":40000,"fdl_TotalPrice":520000,"ShamsiDate":"1396/07/27","mrq_No":"4068","drq_SaleSplName":"F P","mrq_Requester_dep_title":"MH","fdl_Num":13},{"slr_Name":"Seasson Store","fct_Date":"10/21/2017","fdl_UniyPrice":35000,"fdl_TotalPrice":4060000,"ShamsiDate":"1396/07/29","mrq_No":"4068","drq_SaleSplName":"F P","mrq_Requester_dep_title":"MH","fdl_Num":116},{"slr_Name":"Seasson Store","fct_Date":"10/21/2017","fdl_UniyPrice":29000,"fdl_TotalPrice":2610000,"ShamsiDate":"1396/07/29","mrq_No":"3898","drq_SaleSplName":"F P","mrq_Requester_dep_title":"MH","fdl_Num":90},{"slr_Name":"Seasson Store","fct_Date":"10/22/2017","fdl_UniyPrice":30000,"fdl_TotalPrice":180000,"ShamsiDate":"1396/07/30","mrq_No":"4068","drq_SaleSplName":"F P","mrq_Requester_dep_title":"MH","fdl_Num":6},{"slr_Name":"Aba Store","fct_Date":"10/23/2017","fdl_UniyPrice":30000,"fdl_TotalPrice":300000,"ShamsiDate":"1396/08/01","mrq_No":"1","drq_SaleSplName":"","mrq_Requester_dep_title":"Store","fdl_Num":10},{"slr_Name":"Aba Store","fct_Date":"4/8/2018","fdl_UniyPrice":32000,"fdl_TotalPrice":224000,"ShamsiDate":"1397/01/19","mrq_No":"4142","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"EST","fdl_Num":7},{"slr_Name":"Hob Store","fct_Date":"5/12/2018","fdl_UniyPrice":32000,"fdl_TotalPrice":96000,"ShamsiDate":"1397/02/22","mrq_No":"2","drq_SaleSplName":"MBA","mrq_Requester_dep_title":"Center Off","fdl_Num":3}];

//var parseDate = d3.timeFormat("%m %d %Y").parse;

myData.forEach(function (d) {
	var parseDate = d3.timeParse("%m/%d/%Y")
	d.fct_Date = parseDate(d.fct_Date);
	/* var newD = new Date(d.fct_Date);
	var jdate3 = new JDate(newD); */
	formatYear = d3.timeFormat("%Y");
	d.year = formatYear(d.fct_Date);
});

var data = crossfilter(myData);
var sellers = [];
for (var d in myData) {
	if (sellers.indexOf(myData[d].slr_Name) == -1) {
		sellers.push(myData[d].slr_Name);
	}
}
// var colorScale = d3.scale.ordinal().range(['#DB0A5B', '#F62459', '#F1A9A0', '#2C3E50', '#26A65B', '#E87E04', '#D35400']);
var sellerDimension = data.dimension(function (d) {
		return d.slr_Name;
	});
var sellerGroup = sellerDimension.group().reduceSum(function (d) {
		return d.fdl_Num;
	});
var sellerChart = dc.barChart('#sellerChart');
sellerChart
.height(300)
.width(700)
.dimension(sellerDimension)
.group(sellerGroup)
.x(d3.scaleBand())
.xUnits(dc.units.ordinal)
.xAxisLabel('Sellers')
.yAxisLabel('Count')
.elasticX(true)
.elasticY(true)
.xAxis().ticks(3);

sellerChart.title(function (d) {
	return d.key + ' : ' + d.value;
});
// sellerChart.xAxis().tickValues([]);

// sellerChart.colors(d3.scale.ordinal().range(colorScale));


/**************************************************/
/* Data Unit Price Chart
/**************************************************/
var dateDimension = data.dimension(function (d) {
		return d.fct_Date;
	});
var unitPriceGroup = dateDimension.group().reduce(
		//return d.fdl_UniyPrice;
		//add
	function (p, v) {
        ++p.count;
        p.total += v.fdl_UniyPrice;
        if (p.count == 0) {
            p.average = 0;
        } else {
            p.average = p.total / p.count;
        }
        return p;
    },
	// remove
    function (p, v) {
        --p.count;
        p.total -= v.fdl_UniyPrice;
        if (p.count == 0) {
            p.average = 0;
        } else {
            p.average = p.total / p.count;
        }
        return p;
    },
	// initial
    function () {
        return {
            count: 0,
            total: 0,
            average: 0
        };
	}
);
var formatTime = d3.timeFormat("%B %d, %Y");
var minDate = formatTime(dateDimension.bottom(1)[0].fct_Date);
var maxDate = formatTime(dateDimension.top(1)[0].fct_Date);

var dateUnitPriceChart = dc.lineChart('#dateunitprice');
dateUnitPriceChart
.height(250)
.width(700)
.curve(d3.curveStepAfter)
.renderArea(true)
.dimension(dateDimension)
.group(unitPriceGroup)
.brushOn(true)
.elasticX(true)
.elasticY(true)
.valueAccessor(function (d) {
	return d.value.average;
})
.x(d3.scaleTime());

/* dateUnitPriceChart.xAxis().tickFormat(function (val) {
	console.log(val);
	var newD = new Date(val);
	var jdate3 = new JDate(newD);
	if (jdate3.getMonth() === 1) {
		return jdate3.format('YYYY'); //return a year in number like 1396
	}
	return jdate3.format('MMMM'); //return month like azar
}); */
dateUnitPriceChart.margins().left = 70;

/** Create Data Table */
var datatable = dc.dataTable('#data-table');

datatable
.dimension(sellerDimension)
.group(function (d) {
	return d.year;
})
.size(10000)
// dynamic columns creation using an array of closures
.columns([
		function (d) {
			return d.mrq_No
		},
		function (d) {
			return d.mrq_Requester_dep_title
		},
		function (d) {
			return d.drq_SaleSplName
		},
		function (d) {
			return d.slr_Name
		},
		function (d) {
			//return d.fct_Date.getFullYear() + ' ' + d.fct_Date.getMonth() + ' ' + d.fct_Date.getDate();
			return d.ShamsiDate;
		},
		function (d) {
			return d.fdl_UniyPrice.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ","); ;
		},
		function (d) {
			return d.fdl_Num;
		}
	])
.showGroups(false)
.sortBy(function (d) {
	return d.slr_Name;
})
.order(d3.ascending);

datatable.title(function (d) {
	return d.key + ' : ' + d.value;
});

datatable.ordering(function (d) {
	return d.ShamsiDate
})
/***********************/

dc.renderAll();
<link href="https://dc-js.github.io/dc.js/css/dc.css" rel="stylesheet"/>
<script src="https://dc-js.github.io/dc.js/js/crossfilter.js"></script>
<script src="https://d3js.org/d3.v5.min.js"></script>
<script src="https://dc-js.github.io/dc.js/js/dc.js"></script>
</script>

<div style="margin-top: -50px;">
        <div class="divForms" style="border: solid; border-color: gray; border-radius: 5px; border-width: 1px; text-align:center; padding-left:20px;">
            <div style="margin-bottom: 20px; width: 100%;">
                <div style="text-align: right; padding-right: 20px; float: right; width: 44%;">
                    <a id="lnk_Reset" href="javascript:dc.filterAll(); dc.renderAll();">Reset</a>
                </div>
                <div style="text-align: center; float: right;">
                    <span>کالا : </span>
                    <b>
                        <asp:Label ID="lbl_Good" runat="server" Text=""></asp:Label></b>
                </div>
            </div>
            <div id="sellerChart" style="direction: ltr !important"></div>
            <div id="dateunitprice" style="direction: ltr !important"></div>
            <table class="table table-condensed table-striped table-bordered table-hover no-margin" border="1" id="data-table" style="width: 100%; border-collapse: collapse; margin-left:5px;">
                <thead>
                    <tr class="HeaderStyle" style="color: #6484E5;">
                        <th class="text-center" scope="col">Num Request</th>
                        <th class="text-center" scope="col">Unit Requested</th>
                        <th class="text-center" scope="col">User</th>
                        <th class="text-center" scope="col">Seller</th>
                        <th class="text-center" scope="col">Date</th>
                        <th class="text-center" scope="col">Unit Price</th>
                        <th class="text-center" scope="col">Count</th>
                    </tr>
                </thead>
            </table>
        </div>
    </div>
  • Build with latest version of dc and d3
  • Bar chart replaced with line chart (Get narrow line for each date)