Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re^2: Unable to create a Pivot chart with visiable Columns

by chandantul (Scribe)
on Sep 09, 2018 at 07:03 UTC ( [id://1221965]=note: print w/replies, xml ) Need Help??


in reply to Re: Unable to create a Pivot chart with visiable Columns
in thread Unable to create a Pivot chart with visiable Columns

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.

  • Comment on Re^2: Unable to create a Pivot chart with visiable Columns

Replies are listed 'Best First'.
Re^3: Unable to create a Pivot chart with visiable Columns
by poj (Abbot) on Sep 09, 2018 at 12:10 UTC

    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]

        You need to change the structure to $pivot{$app}{$device}{$browser}

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

# set up with test data
my @responseid; my @responsdisp; my @responsalter;
my @responseclientdevice; my @responseclientbrowser;
my @responseapps5; my @estdate;
test_data();

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

my $worksheet1 = $book->add_worksheet('Data');
my @header = ('User Id','User Display Name','User Email Id','Device',
              'Browser','Application Name','Timestamp'); 
$worksheet1->write(0,0, \@header, $format);          
            
my $row1 = 1;
my %pivot=(); # pivot table count
my %categ=();
my %app  =();

for my $k (0..$#responseid)  {
  my @f = (
   $responseid[$k],  #0
   $responsdisp[$k], #1
   $responsalter[$k],#2
   $responseclientdevice[$k], #3
   $responseclientbrowser[$k],#4
   $responseapps5[$k],        #5
   $estdate[$k],              #6
  );
  $worksheet1->write($row1,0, \@f);
   
  my $device  = $f[3];
  my $browser = $f[4];
  my $app     = $f[5];
        
  ++$pivot{$app}{$device}{$browser};
  ++$categ{'device'}{$device};
  ++$categ{'browser'}{$browser}; 
  ++$app{$app};
    
  $row1++;
  #print Dumper $app;   
}

# create summary pivot table
my $worksheet2 = $book->add_worksheet('Summary');
my @device  = sort keys %{$categ{'device'}};
my @browser = sort keys %{$categ{'browser'}};
my @applist = sort keys %app;

$row1 = 0;
for my $device (@device){
  $worksheet2->write($row1++,0,[$device,@applist]);
  for my $browser (@browser){
    my $col = 0;
    $worksheet2->write($row1,0,$browser);
    for my $app (@applist){
      $worksheet2->write($row1,++$col,$pivot{$app}{$device}{$browser})
+;
    }
    ++$row1;
  }
  ++$row1;
}

sub test_data {
  while (<DATA>)  {
    chomp;
    next unless /\S/;
    my ($userid,$device,$browser,$app) = split ';',$_;
    push @responseid,$userid;
    push @responseclientdevice,$device;
    push @responseclientbrowser,$browser;
    push @responseapps5,$app; 
  }
}
__DATA__
user1;Mobile;Chrome;AppA
user2;Mobile;IE11;AppB
user3;Computer;IE11;AppC
user4;Mobile;Chrome;AppD
user5;Computer;Firefox;AppA
user6;Tablet;IE11;AppC
user7;Tablet;Safari;AppE
user8;Computer;Safari;AppE
user9;Computer;Safari;AppE

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

      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]

        I'm sorry I don't understand your requirement. Do you mean adding a line on the chart like this ?

        
 5 |      y
 4 |  y   y  x            x app1
 3 |  y  xy  x        y   y app2
 2 |  yz xy  xy   y   yz  z app3
 1 | xyz xyz xyz xyz xyz
   ------------------------------
     com lap tab br1 br2 
      insert line here ?
         Device/Browser
        poj
[reply]

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://1221965]
help
Chatterbox?
and the web crawler heard nothing...

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

    No recent polls found