Pivot aggregate - count distinct

Ralf Föhring commented on Oct 2, 2019

Hi,

is there a way to implement a 'count distinct' aggregate function in pivot/pivot matrix?

David Winterburn commented on Oct 3, 2019

Hi Ralf,

Probably could try the customAggregates property of Pivot process:

        $distinctValues = [];
        ...
        ->pipe(new Pivot(array(
            "dimensions" => array(
                "column" => ...,
                "row" => ...,
            ),
            "aggregates" => array(
                "distinctCount" => "field1",
            ),
            "customAggregates" => [
                "distinctCount" => [
                    "{initValue}" => 0,
                    "{aggValue}" => function($aggValue, $value, $field, $row) use (& $distinctValues) {
                        if (! in_array($value, $distinctValues) {
                            $aggValue++;
                            array_push($distinctValues, $value);
                        }
                        return $aggValue;
                    },
                ]
                
                
            ],
        )))
        ...

Let us know if it works for you. Thanks!

Ralf Föhring commented on Oct 8, 2019

No, it is not working. It's only showing zeros. Is the $distinctValues resetting for each level, as a normal aggregate would do?

Here is my view configuration:

'waitingFields' => array(
                'factor - sum' => 'data', 
                'detail_id - count' => 'data', 
                'order_id - distinctCount' => 'data',

David Winterburn commented on Oct 9, 2019

Hi Ralf,

To count distinct values to each cell level, I think we need some changes in the Pivot process as well. Please open the file pivot/processes/Pivot.php and replace these lines:

...
$datum[$op] = $this->aggValue($op, $datum[$op], $row[$af], $af, $row);
...
$aggValue = is_callable($func) ?
            $func($aggValue, $value, $af, $row) : $aggValue;

with these ones:

...
$datum[$op] = $this->aggValue($op, $datum[$op], $row[$af], $af, $row, $dn);
...
$aggValue = is_callable($func) ?
                $func($aggValue, $value, $af, $row, $dn) : $aggValue;

Finally, use the custom aggregate like this:

    use \koolreport\core\Utility as Util;
    ...
        $distinctValues = [];
        ...
        ->pipe(new Pivot(array(
            "dimensions" => array(
                "column" => ...,
                "row" => ...,
            ),
            "aggregates" => array(
                "distinctCount" => "field1",
            ),
            "customAggregates" => [
                "distinctCount" => [
                    "{initValue}" => 0,
                    "{aggValue}" => function($aggValue, $value, $field, $row, $dn) use (& $distinctValues) {
                        $cellDistinctValues = Util::init($distinctValues, $dn, []);
                  if (! in_array($value, $cellDistinctValues)) {
                      $aggValue++;
                      array_push($distinctValues[$dn], $value);
                  }
                        return $aggValue;
                    },
                ]
                
                
            ],
        )))
        ...

Let me know your result. Thanks!

Ralf Föhring commented on Oct 11, 2019

It does the magic. Thanks!

KoolReport's Forum

Pivot aggregate - count distinct #1110

Build Your Excellent Data Report