Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: Unable to create a Pivot chart with visiable Columns

by poj (Abbot)
on Sep 08, 2018 at 10:38 UTC ( [id://1221947]=note: print w/replies, xml ) Need Help??


in reply to Unable to create a Pivot chart with visiable Columns

I'm not clear what you are trying to do but to create a PivotChart you first need to create a pivot table. Are you trying to create a bar chart of counts for browser types like this ?


#!/usr/bin/perl
use strict;
use Excel::Writer::XLSX;

my $strExcelFilename= 'chart.xlsx';
my $book = Excel::Writer::XLSX->new($strExcelFilename);
my $format = $book->add_format(); 

my $sheet1 = $book->add_worksheet('Data');
my @header = ('User ID','Browser'); 
for my $col(0..$#header){
  $sheet1->write(0,$col, $header[$col], $format);
}              
            
my $row = 1;
my %count=(); # browser count
while (<DATA>)  {
  chomp;
  next unless /\S/;
  my @f = split ';',$_;
  ++$count{$f[1]};
  $sheet1->write_row($row++,0, \@f);
}

# add summary chart
my $sheet2 = $book->add_worksheet('Summary');
$sheet2->write_row(0,0,['Browser','Count']);
$row = 1;
for (sort keys %count){
  $sheet2->write_row($row++,0,[$_,$count{$_}]);
}

my $chart = $book->add_chart( type => 'column', embedded => 1  );
$chart->add_series(
  name       => 'Count',    
  categories => '=Summary!$A$2:$A$'.$row,
  values     => '=Summary!$B$2:$B$'.$row,
);

$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Browser' );
$chart->set_y_axis( name => 'Count of Computer'  );
$chart->set_style( 11 );

# add chart
$sheet2->insert_chart( 'E2', $chart,  25,  10 );
$book->close();

__DATA__
user1;Chrome
user2;IE11
user3;IE11
user4;Chrome
user5;Firefox
user6;IE11
user7;Safari

[download]

Replies are listed 'Best First'.
Re^2: Unable to create a Pivot chart with visiable Columns
by chandantul (Scribe) on Sep 09, 2018 at 07:03 UTC

    Thanks for your kind responses! I would like to create a Column chart first that will consists of Application Names as legends and Browser and Device columns should be in Categories. Application Names are 10 types in my data but there are 1200 entries for different users at different times of accesses hence I would like to count of users in OKTA ID columns ( 1200 ) and check how many times different applications was accessed by different browsers ( Chrome, IE11, Firefox or Safari) and Devices ( Computer or Mobile). Hope I was able to make you understand my intention. I was unable to figure it as I am bit new in Excel Writer Chart and I am using Windows 7 64 bits . Please help if possible.

[reply]

      Ok, see if this is closer

      #!/usr/bin/perl
use strict;
use Excel::Writer::XLSX;

my $strExcelFilename= 'chart.xlsx';
my $book = Excel::Writer::XLSX->new($strExcelFilename);
my $format = $book->add_format(); 

my $sheet1 = $book->add_worksheet('Data');
my @header = ('User ID','Device','Browser','Application'); 
for my $col(0..$#header){
  $sheet1->write(0,$col, $header[$col], $format);
}              
            
my $row = 1;
my %pivot=(); # pivot table count
my %categ=();
my %app  =();
while (<DATA>)  {
  chomp;
  next unless /\S/;
  my @f = split ';',$_;
  my ($userid,$device,$browser,$app) = @f;
  ++$pivot{$app}{$device};
  ++$pivot{$app}{$browser};
  ++$categ{'device'}{$device};
  ++$categ{'browser'}{$browser};  
  ++$app{$app};
  $sheet1->write_row($row++,0, \@f);
}

# create summary pivot table
my $sheet2 = $book->add_worksheet('Summary');
my @device  = sort keys %{$categ{'device'}};
my @browser = sort keys %{$categ{'browser'}};
my @applist = sort keys %app;
$sheet2->write_row(0,1,\@applist);
$row = 1;
for my $categ (@device,@browser){
  my $col = 0;
  $sheet2->write($row,$col++,$categ);
  for my $app (@applist){
    $sheet2->write($row,$col++,$pivot{$app}{$categ});
  }
  ++$row
}

my $chart = $book->add_chart( type => 'column', embedded => 1  );
my $col = 'B';
for my $app (sort keys %app){
  $chart->add_series(
    name       => $app,    
    categories => '=Summary!$A2:$A'.$row,
    values     => '=Summary!$'.$col.'$2:$'.$col.$row,
  );
  ++$col;
}

$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Device/Browser' );
$chart->set_y_axis( name => 'User Count'  );
#$chart->set_style( 11 );

# add chart
$sheet2->insert_chart( 'A'.($row+2), $chart,  0,  0 );
$book->close();

# user;device;browser;app
__DATA__
user1;laptop;Chrome;AppA
user2;laptop;IE11;AppB
user3;desktop;IE11;AppC
user4;laptop;Chrome;AppD
user5;laptop;Firefox;AppA
user6;laptop;IE11;AppC
user7;desktop;Safari;AppE
user8;desktop;Safari;AppE
user9;desktop;Safari;AppE

      [download]
      poj
[reply]
[d/l]

        You are a genius!! I have figured it out finally and resolved the issue after long time. Got a better understanding on the Chart. You are realyy helpful Thanks a lot!! My code below

                         $strExcelFilename = "D:/PERL/" . "DEVICE-BROWSER-REPO
+RT" . ".xlsx";
                        
        #$workbook1 = Spreadsheet::WriteExcel->new($strExcelFilename);
        $workbook1 = Excel::Writer::XLSX->new($strExcelFilename);
        #$worksheet1 = $workbook1->addworksheet("DEVICE-BROWSER-LIST")
+;
        $worksheet1 = $workbook1->add_worksheet('Data');
        #$worksheet2 = $workbook1->add_worksheet('Summary');
        $row1= 1;
        
        #$chart = $workbook1->add_chart( type => 'column', embedded =>
+ 1 );
            
            foreach $worksheet1 ($workbook1->sheets()) {
         if( $worksheet1->get_name() eq $SUCCESS_COUNT_SHEET_NAME ) {
              $format = $workbook1->add_format(%header);
           $worksheet1->write(0,0, "User Id", $format);
           $worksheet1->write(0,1, "User Display Name", $format);
           $worksheet1->write(0,2, "User Email Id", $format);
           $worksheet1->write(0,3, "Device", $format);
           $worksheet1->write(0,4, "Browser", $format);
           $worksheet1->write(0,5,"Application Name", $format);
           $worksheet1->write(0,6,"TimeStamp", $format);
           #$worksheet1->write(0,7, "EMAIL--ITUSER-STATUS", $format);
      }
      }
      for ($k = 0; $k <= $#responseid; $k++)  {
    
         
        $worksheet1->write($row1,0, $responseid[$k]);
        $worksheet1->write($row1,1, $responsdisp[$k]);
        $worksheet1->write($row1,2, $responsalter[$k]);
        $worksheet1->write($row1,3, $responseclientdevice[$k]);
        $worksheet1->write($row1,4, $responseclientbrowser[$k]);
        $worksheet1->write($row1,5, $responseapps5[$k]);
        $worksheet1->write($row1,6, $estdate[$k]);
        $app = $responseapps5[$k];
        $device = $responseclientdevice[$k];
        $browser = $responseclientbrowser[$k];
        $userid = $responseid[$k];
        
        ++$pivot{$app}{$device};
        ++$pivot{$app}{$browser};
        ++$categ{'device'}{$device};
        ++$categ{'browser'}{$browser}; 
        ++$app{$app};
    
    $row1++;
    #print Dumper $app;
    
    }
    
    $worksheet2 = $workbook1->add_worksheet('Summary');
my @device  = sort keys %{$categ{'device'}};
my @browser = sort keys %{$categ{'browser'}};
my @applist = sort keys %app;
#print Dumper @applist;
$worksheet2->write_row(0,1,\@applist);
$row1 = 1;
for my $categ (@device,@browser){
  my $col = 0;
  $worksheet2->write($row1,$col++,$categ);
  for my $app (@applist){
    $worksheet2->write($row1,$col++,$pivot{$app}{$categ});
  }
  ++$row1
}

my $chart = $workbook1->add_chart( type => 'column', embedded => 1  );
my $col = 'B';
for my $app (sort keys %app){
  $chart->add_series(
    name       => $app,    
    categories => '=Summary!$A2:$A'.$row1,
    values     => '=Summary!$'.$col.'$2:$'.$col.$row1,
  );
  ++$col;
}

$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Device/Browser' );
$chart->set_y_axis( name => 'User Count'  );
$worksheet2->insert_chart( 'A'.($row1+2), $chart,  0,  0 );
    $workbook1->close();

        [download]
[reply]
[d/l]

        Hi, As per the Latest code result the table coming like this. Computer Mobile Tablet CHROME EDGE FIREFOX IE11 IE7 SAFARI UNKNOWN --- but I need Computer and Mobile as a Device and there row label will show how many time different browser was accessed for individual applications like before. Please check below example. Computer App 1 App 2 App3 Browser 1 2 3 11 Browser 2 Browser 3 Mobile Browser 1 3 4 12 Browser 2 Browser 3 Mobile Browser 1 4 5 15 Browser 2 Browser 3 This is the labeling I needed. Can you please help in my existing code that I shared with you? Please check below my current table code snippet 

            $worksheet2 = $workbook1->add_worksheet('Summary');
my @device  = sort keys %{$categ{'device'}};
my @browser = sort keys %{$categ{'browser'}};
my @applist = sort keys %app;
#print Dumper @applist;
$worksheet2->write_row(0,1,\@applist);
$row1 = 1;
for my $categ (@device,@browser){
  my $col = 0;
  $worksheet2->write($row1,$col++,$categ);
  for my $app (@applist){
    $worksheet2->write($row1,$col++,$pivot{$app}{$categ});
  }
  ++$row1
}

        [download]
[reply]
[d/l]

        Thanks a lot Man!! I wanted to show like for all applications and the Computer , Mobile and other devices should be isolated from Browser names. Can we add this below in the Chart itself above Device/Browser but below browser's names in X axes? Grand Total 2 1 195 72 5 45 59 12 370 6 19 514 3 1303 Do I need to add the total count like below? Can you please help?

         for (sort keys %count){
  $sheet2->write_row($row++,0,[$_,$count{$_}]);
}

        [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://1221947]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others wandering the Monastery: (6)
As of 2024-04-19 10:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found