Jump to content

Google Column Chart data display month wise


Senthilkumar

Recommended Posts

Dear Team,

I am using Google column chart on my dahboard for displaying the datat from mysql databse.

The chart h axis i want to display all the month by default like bellow image and for which month the data is available that only diplay here like bellow image.

image.png.0a773827a8442de552fb6dc3bf6b6f74.png

 

Iam using the bellow query for fetch the data from database 

 <?php;

                             $query = "SELECT revenue.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM 
                                        ( select Month, sum(Revanue) as Revanue  from dbms.revenue_data where dealerid = '$Emp_No' AND Status = '1' group by Month ) revenue 
                                        LEFT JOIN ( select Month, sum(Total) as EmployeeExpense  from dbms.employeeexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) empExp USING(Month)
                                        LEFT JOIN ( select Month, sum(Total) as InfraExpense  from dbms.infrastructure_expense where dealerid = '$Emp_No' AND Status = '1' group by Month ) infExp USING(Month)
                                        LEFT JOIN ( select Month, sum(Total) as OtherExpense  from dbms.otherexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) OtherExpense USING(Month);";

                             $exec = mysqli_query($conn, $query);
                             while ($row = mysqli_fetch_array($exec)) {
                                 $Month = $row['Month'];
                                 $Revanue = $row['Revanue'];
                                 $EmployeeExpense= $row['EmployeeExpense'];
                                 $InfraExpense = $row['InfraExpense'];
                                 $OtherExpense = $row['OtherExpense'];

                                 $formatedMonth = date("M-Y", strtotime($Month));

                                 $Expenditure = $EmployeeExpense + $InfraExpense + $OtherExpense;

                                 $Absorption_Ratio = ($Revanue / $Expenditure )*100;

                                 echo "['$formatedMonth',$Absorption_Ratio],";
                             }
                             ?>

I am getting output like below image 

image.thumb.png.a429fd06a98ed81789107ed815c7ba09.png

 

This is displaying the month only where there is data available. 

How can i change it to display all the months on haxis

Link to comment
Share on other sites

35 minutes ago, Senthilkumar said:

This is displaying the month only where there is data available.

Of course it is! How can you expect the results to contain data that isn't there?

You can attack the problem from the input end or the output end of the process.

  • Input method
    Create a temporary table `tempdate` (id , month) with 12 rows - one for each month - and left join the subqueries to this.
  • Output method
    create an array with key for each month and store your result's totals in this array. Create chart from the array data.

NOTE: If your Month column is datetime time (which it appears to be as you reformat using strtotime) your queries are are grouping and joining by day and not by month (unless you are always storing the first day of the month, which we can't see)

Link to comment
Share on other sites

Dear Brand,

I have created the separeate table for month and changed the query with left join

 <?php

                             $query = "SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM 
                                ( select Month from dbms.month) mnth 
                                LEFT JOIN ( select Month, sum(Revanue) as Revanue  from dbms.revenue_data where dealerid = '$Emp_No' AND Status = '1' group by Month ) revenue USING(Month)
                                LEFT JOIN ( select Month, sum(Total) as EmployeeExpense  from dbms.employeeexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) EmployeeExpense USING(Month)
                                LEFT JOIN ( select Month, sum(Total) as InfraExpense  from dbms.infrastructure_expense where dealerid = '$Emp_No' AND Status = '1' group by Month ) InfraExpense USING(Month)
                                LEFT JOIN ( select Month, sum(Total) as OtherExpense  from dbms.otherexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) OtherExpense USING(Month);";

                                 $exec = mysqli_query($conn, $query);
                                 while ($row = mysqli_fetch_array($exec)) {
                                     $Month = $row['Month'];
                                     $Revanue = $row['Revanue'];
                                     $EmployeeExpense= $row['EmployeeExpense'];
                                     $InfraExpense = $row['InfraExpense'];
                                     $OtherExpense = $row['OtherExpense'];

                                     $formatedMonth = date("M-Y", strtotime($Month));

                                     $Expenditure = $EmployeeExpense + $InfraExpense + $OtherExpense;

                                     if ($Expenditure != 0 && $Revanue != 0) {
                                         $Absorption_Ratio = ($Revanue / $Expenditure) * 100;
                                     }else{
                                         $Absorption_Ratio = 0;
                                     }
                                     echo "['$formatedMonth',$Absorption_Ratio],";
                                 }
                             ?>

I am getting 12 columns on the graph. For testing i updated the datas for the month of Jan, Feb, Mar and Jun. My grap the month is displaying Jan,Feb,Mar,Jun,Apr,May,Jul,Aug,Sep,Oct,Nov,Dec

image.thumb.png.7aed33952b95420d5edd18274f224e69.png

Can you please tell me how to correct this

Link to comment
Share on other sites

If your Month table looks something like this

+-----+--------------+
| id  |  Month       |
+-----+--------------+
|  1  | Jan-23       |
|  2  | Feb-23       |
|  3  | Mar-23       |
|  4  | Apr-23       |
|  5  | May-23       |
|  6  | Jun-23       |
|  7  | Jul-23       |
|  8  | Aug-23       |
|  9  | Sep-23       |
| 10  | Oct-23       |
| 11  | Nov-23       |
| 12  | Dec-23       |
+-----+--------------+

then you can ORDER BY mnth.id

How are you storing Month in those tables?

Do you have a separate column for actual transaction date?

Link to comment
Share on other sites

I didnt noticed that. Now it is working properly

SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM 
( select id, Month from dbms.month ) mnth 
LEFT JOIN ( select  Month, sum(Revanue) as Revanue  from dbms.revenue_data where dealerid = '81019218' AND Status = '1' group by Month ) 
revenue USING(Month)
LEFT JOIN ( select Month, sum(Total) as EmployeeExpense  from dbms.employeeexpense where dealerid = '81019218' AND Status = '1' group by Month ) 
EmployeeExpense USING(Month)
LEFT JOIN ( select Month, sum(Total) as InfraExpense  from dbms.infrastructure_expense where dealerid = '81019218' AND Status = '1' group by Month ) 
InfraExpense USING(Month)
LEFT JOIN ( select Month, sum(Total) as OtherExpense  from dbms.otherexpense where dealerid = '81019218' AND Status = '1' group by Month ) 
OtherExpense USING(Month) ORDER BY mnth.id;

 

image.thumb.png.3108cdb7b75a54f0ad06b188ad000d41.png

 

 

I need one suggession from you. In month table we pre defined the year. How to do this for next year. Is ther any other way to capure the current year on this table

Link to comment
Share on other sites

Here's what I'd suggest then.

Your new "Month" table will contain the month only (Jan, Feb etc with no year, we'll always assume the current year).  Your query becomes

SELECT mnth.Month
     , Revanue
     , EmployeeExpense
     , InfraExpense
     , OtherExpense 
FROM 
    (
        select id
             , concat(month, '-', YEAR(CURDATE())) as Month
        from dbms.month
    ) mnth 
LEFT JOIN 
    ( 
        select  Month
             , sum(Revanue) as Revanue 
        from dbms.revenue_data 
        where dealerid = '81019218' AND Status = '1' 
        group by Month 
    ) revenue USING(Month)
LEFT JOIN 
    (
        select Month
             , sum(Total) as EmployeeExpense  
        from dbms.employeeexpense 
        where dealerid = '81019218' AND Status = '1' 
        group by Month 
    ) EmployeeExpense USING(Month)
LEFT JOIN 
    ( 
        select Month
             , sum(Total) as InfraExpense  
        from dbms.infrastructure_expense 
        where dealerid = '81019218' AND Status = '1' 
        group by Month 
    ) InfraExpense USING(Month)
LEFT JOIN 
    ( 
        select Month
             , sum(Total) as OtherExpense  
        from dbms.otherexpense 
        where dealerid = '81019218' AND Status = '1' 
        group by Month 
    ) OtherExpense USING(Month) 
ORDER BY mnth.id;

 

Link to comment
Share on other sites

Dear Barand,

Thanks for your suggession. It is working now. 

My final query is 

SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM 
( select id, concat(YEAR(CURDATE()), '-', month) as Month from dbms.month ) mnth 
LEFT JOIN ( select  Month, sum(Revanue) as Revanue  from dbms.revenue_data where dealerid = '81019218' AND Status = '1' group by Month ) revenue USING(Month)
LEFT JOIN ( select Month, sum(Total) as EmployeeExpense  from dbms.employeeexpense where dealerid = '81019218' AND Status = '1' group by Month ) 
EmployeeExpense USING(Month)
LEFT JOIN ( select Month, sum(Total) as InfraExpense  from dbms.infrastructure_expense where dealerid = '81019218' AND Status = '1' group by Month ) 
InfraExpense USING(Month)
LEFT JOIN ( select Month, sum(Total) as OtherExpense  from dbms.otherexpense where dealerid = '81019218' AND Status = '1' group by Month ) 
OtherExpense USING(Month) ORDER BY mnth.id;

And table is 

image.png.fcd82ca39b4318a24b9c5dcc1ff9b2d5.png

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.