Virtual column not getting calculated properly

Hi
Can anyone please make me understand why virtualcolumn calculation is behaving like this.

Below is the Druid query which i fired:

{

“queryType” : “groupBy”,

“dataSource” : “NodeB_Raw”,

“granularity” : “minute”,

“intervals” : [ “2019-06-28T11:40:58.128Z/2019-07-01T11:40:58.128Z” ],

“descending” : “true”,

“aggregations” : [ {

"type" : "doubleSumExtended",

"name" : "VIRTUALKPI_NodeB_C_15",

"fieldName" : "VIRTUALKPI_NodeB_C_15"

},{

"type" : "doubleSumExtended",

"name" : "NodeB_C_1",

"fieldName" : "NodeB_C_1"

},{

"type" : "doubleSumExtended",

"name" : "NodeB_C_2",

"fieldName" : "NodeB_C_2"

} ],

“postAggregations” : ,

“virtualColumns” : [ {

"type" : "expression",

"name" : "VIRTUALKPI_NodeB_C_15",

"expression" : "100*(NodeB_C_2/NodeB_C_1)",

"outputType" : "DOUBLE"

} ],

“filter” : {

"type" : "and",

"fields" : [ {

  "type" : "or",

  "dimension" : null,

  "value" : null,

  "function" : null,

  "fields" : [ {

    "type" : "selector",

    "dimension" : "COUNTER_GROUP_ID",

    "value" : "NodeB_CG_31",

    "function" : null,

    "fields" : null,

    "lower" : null,

    "upper" : null,

    "ordering" : null,

    "lowerStrict" : null,

    "upperStrict" : null,

    "intervals" : null,

    "field" : null,

    "values" : null,

    "pattern" : null

  } ],

  "lower" : null,

  "upper" : null,

  "ordering" : null,

  "lowerStrict" : null,

  "upperStrict" : null,

  "intervals" : null,

  "field" : null,

  "values" : null,

  "pattern" : null

} ]

},

“context” : {

"timeout" : null,

"queryId" : "amrUGGo2AA",

"queryPriority" : null

},

“dimensions” : [ {

"type" : null,

"dimension" : "NodeB_P_1",

"outputName" : null,

"extractionFn" : null

}, {

"type" : null,

"dimension" : "COUNTER_GROUP_ID",

"outputName" : null,

"extractionFn" : null

} ],

“limitSpec” : {

"type" : "default",

"limit" : null,

"columns" : [ ]

},

“having” : null

}

The results which i received provides expected results for some of the events and unexpected results for other. Please explain the reason:

Below are the results:

{

    "version": "v1",

    "timestamp": "2019-06-29T06:30:00.000Z",

    "event": {

        "NodeB_C_1": 2910,

        "NodeB_P_1": "EER",

        "VIRTUALKPI_NodeB_C_15": 99.93127147766323,

        "COUNTER_GROUP_ID": "NodeB_CG_31",

        "NodeB_C_2": 2908

    }

},

{

    "version": "v1",

    "timestamp": "2019-06-29T06:45:00.000Z",

    "event": {

        "NodeB_C_1": 3005,

        "NodeB_P_1": "EER",

        "VIRTUALKPI_NodeB_C_15": 99.93344425956738,

        "COUNTER_GROUP_ID": "NodeB_CG_31",

        "NodeB_C_2": 3003

    }

},

{

    "version": "v1",

    "timestamp": "2019-06-29T07:00:00.000Z",

    "event": {

        "NodeB_C_1": 2913,

        "NodeB_P_1": "EER",

        "VIRTUALKPI_NodeB_C_15": "NaN",

        "COUNTER_GROUP_ID": "NodeB_CG_31",

        "NodeB_C_2": 2891

    }

}

Thanks in Advance,

Amit Kumar

Hey Amit,

Just speculating but I suspect NodeB_C_1 is zero at some point resulting in an expression that can’t be evaluated for that segment.

Best regards,

Dylan

Hi Dylan,
For calculating VIRTUALKPI_NodeB_C_15. It is using Node_C_1 and Node_C_2, whose values are printed as 2913 and 2819 respectively.

Please correct me if I am wrong.

Thanks and regards,

Amit Kumar

Hey Amir,

Yeah the sum of those values may be greater than 0 but a virtual column will run an expression on individual values which may have 0. You may want a post aggregation instead?

Currently in your aggregation you label the underlying column the same as the output aggregated value, it’d be worth calling them something different to distinguish between what you’re operating over.

Cheers,

Dylan