Thounsand Separator in Total number Category Metric

John commented on Dec 20, 2023

Although i found how to update thousand separator to columns/segments, i couldn't apply it to the Total number on the right upper side of the Category Metric. Also, helper method (for the tooltip) doesn't work.

KoolReport commented on Dec 25, 2023

Hi John,

Please try to add the formatting to the count field like below:

    protected function fields()
    {
        return [
            $this->group(Text::create("status")),
            $this->count(
                Number::create("status")
                    ->thousandSeparator(".")
                    ->decimalPoint(",")
            )
        ];
    }

John commented on Dec 25, 2023

Thanks but if i put 'count' i get the number of quotes while i want their total value. If i put 'sum' i get error: Message: Division by zero Line: 62 (...dashboard/metrics/Category.view.php)

KoolReport commented on Dec 25, 2023

Could you please post full of your code.

John commented on Dec 25, 2023

Here it is:

<?php

use \koolreport\dashboard\metrics\Category;
use \koolreport\dashboard\fields\Date;
use \koolreport\dashboard\fields\Number;
use \koolreport\dashboard\fields\Text;
use Mydb;

class QuoteMetric extends Category
{
    protected function onCreated()
    {
        $this
        ->type("primary")
		->title("Quotes")
        ->lazyLoading(false);
    }

    protected function dataSource()
    {		
     return Mydb::rawSQL("
	 SELECT quotestage, subtotal 
	 FROM ...");
    }

    protected function fields()
    {
        return [
            $this->group(Text::create("quotestage"))->showTop(5)->andShowOthers(),
	//		$this->count(Number::create("quotestage"))->thousandSeparator('.')->decimalPoint(','),
			$this->sum(Number::create("subtotal"))->showRawValue(true)->decimals(0)->thousandSeparator('.')->decimalPoint(',')
            // $this->count(Text::create("quotestage"))
        ];
    }
}

Sebastian Morales commented on Dec 26, 2023

This error will happen when all your "subtotal" values sum to zero. We will fix it in the next version of Dashboard. Meanwhile you can apply the following fix:

1 . Open the files koolreport/amazing/dashboard/metrics/Category.view.php and koolreport/appstack/dashboard/metrics/Category.view.php, go to the 62nd line and replace this statement:

    $row[$this->measuredField()->colName()]*100/$total

with this one:

    ($total == 0 ? 0 : ($row[$this->measuredField()->colName()]*100/$total))

John commented on Dec 26, 2023

Thanks but i don't have any zero 'subtotal' value. I made the changes to the files you proposed and now when i add $this->count(Number::create("quotestage"))->thousandSeparator('.')->decimalPoint(',') or $this->sum(Number::create("quotestage"))->thousandSeparator('.')->decimalPoint(',') i get zero values on my widget (which is not correct). My initial (see above) image has now become like this:

Sebastian Morales commented on Dec 27, 2023

Would you mind posting your edited Category.view.php content for us to check it for you? Rgds,

John commented on Dec 28, 2023

They are the defaults, only your change added and nothing else. Here is the koolreport/appstack/dashboard/metrics/Category.view.php

<?php 
    use \koolreport\dashboard\Lang; 
    $total = $this->data()->sum($this->measuredField()->colName());
?>
<div id="<?php echo $this->master()->name(); ?>" class="card card-accent-<?php echo $this->master()->type(); ?> card-value">
    <div class="card-body pb-0" style="min-height:142px">
        <div class="row">
            <div class="col">
                <div class="text-muted text-uppercase font-weight-bold font-xs"><?php echo $this->master()->title(); ?></div>    
            </div>
            <div class="col text-right">
                <div class="h5 text-secondary"><?php echo $this->measuredField()->field()->formatValue($total); ?></div>
            </div>
        </div>
        <div>
        <div style="display:inline-block;float:right;position:relative;top:-5px;left:10px;">
        <?php
            \koolreport\d3\DonutChart::create([
                "dataSource"=>$this->donutData(),
                "colorScheme"=>$this->colors(),
                "height"=>$this->master()->pieSize(),
                "width"=>$this->master()->pieSize(),
                "columns"=>[
                    $this->categoryField()->field()->colName(),
                    $this->measuredField()->colName(),
                ],
                "options"=>[
                    "legend"=>[
                        "show"=>false,
                    ],
                    "tooltip"=>[
                        "format"=>[
                            "title"=>"function(){
                                return '".$this->master()->title()."';
                            }",
                        ]
                    ],
                    "donut"=>[
                        "label"=>[
                            "show"=>false
                        ]
                    ]
                ]
            ]);
        ?>

        </div>
        <small>
            <ul class="list-inline">
                <?php foreach($this->data() as $i=>$row): ?>
                    <li>
                        <i class="fa fa-circle" style="color:<?php echo $this->colors()[$i]; ?>"></i> 
                        <?php 
                            $field = $this->categoryField()->field(); 
                            echo $field->row($row)->formattedValue();
                        ?>
                        -
                        <?php
                            echo $this->measuredField()->formatValue(
                                ($this->measuredField()->_showRawValue()===true)?
                                    $row[$this->measuredField()->colName()]:
                     //               $row[$this->measuredField()->colName()]*100/$total,
								($total == 0 ? 0 : ($row[$this->measuredField()->colName()]*100/$total)), //changed
                            $row);
                        ?>
                    </li>
                <?php endforeach; ?>
            </ul>
        </small>

        <div class="row">
            <div class="col-8">
            </div>
            <div class="col-4 text-right">
            </div>
        </div>
        </div>
    </div>
</div>

Sebastian Morales commented on Jan 2

The change is to fix the division by zero issue. In case you might have zero data row then all totals and results are zero as well. Would you pls add the following lines at the beginning of the Category.view.php and see the dashboard:

<?php 
    use \koolreport\dashboard\Lang; 
    print_r($this->data()); //add this line
    $total = $this->data()->sum($this->measuredField()->colName());
    print_r($total); //add this line
    ...

John commented on Jan 2

Thanks, yes i might have data lines with zero values. I added the lines and i got errors 'Unexpected response' without any other details. Error loaded 2 times.

Sebastian Morales commented on Jan 3

Ah, the print_r command was just to check the data of Category metrics. You can see the printed data in the error dialog. If you have non-zero data rows and 0 values metrics pls let us know.

John commented on Jan 3

As i said, there aren't any other details in the error dialog. Just a flash of 'Unexpected response'

Sebastian Morales commented on Jan 4

To see full messages in Dashboard you can turn on debug mode like this in App.php:

class App extends \koolreport\dashboard\Application
{
    protected function onCreated()
    {
        $this->debugMode(true)
        ...

John commented on Jan 4

Thanks but I've already set this.

Sebastian Morales commented on Jan 4

If so pls remove the print_r line and check your metrics' source data like this:

class MyCategory extends Category
{
    protected function dataSource()
    {
        $ds = Mydb::rawSQL("
	             SELECT quotestage, subtotal 
	             FROM ...")
                ->run()
                ; 
        print_r($ds->data());
        return $ds;
    }

John commented on Jan 4

Thanks, i get something like this:

Array ( [0] => Array ( [quotestage] => Delivered [subtotal] => 780.00000000 ) [1] => ... A LONG ARRAY HERE ... Array ( [quotestage] => Accepted [subtotal] => 19000.00000000 ) ) {"panels":{"QuoteMetric":[" \n \n [Widget] QuoteMetric\n\n \n \n

Sebastian Morales commented on Jan 5

I saw that "quotestage" field's values are all null or empty here. That might be why your sum based on "quotestage" all return zero:

    $this->sum(Number::create("quotestage"))->thousandSeparator('.')->decimalPoint(',')

Did you mean to count or sum based on "subtotal" field instead?

John commented on Jan 5

Hmm.... quotestage = Delivered, quotestage = Accepted etc. I don't know what you mean exactly... With my query i estimate the subtotal per quotestage. If you see on my image above the sum of quotes in quotestage Delivered is 6.835.562, with quotestage Created sum is 1.743.253 etc. Total sum of all quotes (in all quotestages) is 8.979.992

Sebastian Morales commented on Jan 8

You can count data fields of any type but can only sum numeric fields. If quotestage = Delivered, Accepted, etc you can not sum it (the total result would be zero):

$this->sum(Number::create("quotestage"))... //this would return zero

You can use either:

$this->count(Number::create("quotestage"))...

or:

$this->sum(Number::create("subtotal"))...

If either of these return wrong results let us know.

John commented on Jan 12

I tried both and it shows this error:

Message: Call undefined showTop() method Line: 23 File: /vendor/koolreport/dashboard/TMagicMethod.php

The point is that - as you can see at my first image above - i can sum non numerical field ( by $this->group(Text::create("quotestage")) ). All numbers are correct, i just want to settle the thousand separator.

Sebastian Morales commented on Jan 15

I think I made a mistake reading your description from the first post. You meant to format the upper right side total value, not the left side individual values.

Ok, so the total value is formatted according to the field setting, not the sum's or count's one. Thus, in your case you can try to format both the numeric field and its grouping one like this:

    protected function fields()
    {
        return [
            $this->group(Text::create("quotestage"))->showTop(5)->andShowOthers(),
            $this->sum(Number::create("subtotal")->decimals(0)->thousandSeparator('.')->decimalPoint(','))
                ->showRawValue(true)->decimals(0)->thousandSeparator('.')->decimalPoint(',')
        ];
    }

John commented on Jan 15

Yes, it was so simple thanks!

KoolReport's Forum

Thounsand Separator in Total number Category Metric #3214

Build Your Excellent Data Report