APPROX COUNT DISTINCT is not working as expected


#1

Query generated by Mapd - Immerse Dashboard
SELECT device_category as key0,device_type as key1,APPROX_COUNT_DISTINCT(ipv4) AS val FROM db_26777a04518e38d8_105268779 WHERE ((dateTime_human >= TIMESTAMP(0) '2018-04-03 00:00:00' AND dateTime_human <= TIMESTAMP(0) '2018-04-06 00:00:00')) GROUP BY key0, key1 ORDER BY val DESC

But whereas when I am trying to implement same BAR CHART using stackedBarChart Example –
Here’s the query that it generates –

SELECT date_trunc(day, dateTime_human) as key0,COUNT(*) AS val FROM db_26777a04518e38d8_105268779 WHERE (dateTime_human &gt;= TIMESTAMP(0) '2018-04-02 16:19:20' AND dateTime_human &lt;= TIMESTAMP(0) '2018-04-24 12:17:33') GROUP BY key0 ORDER BY key0

and here’s my multi reduce expression object
var reduceMultiExpression3 =
[
{
expression: “ipv4”,
agg_mode: “approx_count_distinct”,
name: “val”
},
{
expression: “dateTime_human”,
agg_mode: “min”,
name: “minimum”
},
{
expression: “dateTime_human”,
agg_mode: “max”,
name: “maximum”
}
];

crossFilter
.groupAll()
.reduceMulti(reduceMultiExpression3)
.valuesAsync(true).then(function (timeChartBounds) {

    var timeChartDimension = crossFilter.dimension("dateTime_human");
    console.log('timeChartDimension');
    console.log(timeChartDimension);
    var timeChartGroup = timeChartDimension.group().reduceCount();
    console.log('timeChartGroup');
    console.log(timeChartGroup);
    var dcTimeChart =
      dc.barChart('.stacked-bar-example')
        .width(800)
        .height(800)
        .elasticY(true)
        .renderHorizontalGridLines(false)
        .brushOn(true)
        .centerBar(true)
        .xAxisLabel('Date')
        .yAxisLabel('# Households')
        .dimension(timeChartDimension)
        .group(timeChartGroup)
        // .ordinalColors(colorScheme)
        .ordinalColors(['#e8b250'])
        .binParams({
            numBins: 400,
            binBounds: [timeChartBounds.minimum, timeChartBounds.maximum]
        });

    dcTimeChart
        .x(d3.time.scale.utc().domain([timeChartBounds.minimum, timeChartBounds.maximum]))
        .yAxis()
        .ticks(5);

    dcTimeChart
        .xAxis()
        .scale(dcTimeChart.x())
        .tickFormat(dc.utils.customTimeFormat)
        .orient('bottom');

    /* now that all the charts are defined, we render */
    dc.renderAllAsync();
  });

can you tell me what I’m doing wrong.


#2

@siddhartha sorry we let this one go without a response for awhile. I’m not totally clear on what problem you’re explaining. Can you provide more details about what you’re hoping to achieve and what you’re getting instead? Perhaps with a sample of the data, or a sample of the visual output?


#3

@easy, I reached out to OmniSci support team on this. They helped me with their expertise to resolve my issue.


#4

Great! I’ll follow up with them to see if there’s anything useful we can post here for the benefit of the community.